Sheet1.UsedRange Weirdness

Question

My Sheet1 contains data in the top 6 rows x 5 columns.

I added a CommandButton to manipulate this data, but when I try to use Sheet1.UsedRange it is returning a Range that is 121 rows x 44 columns.

I've inspected that cell in Sheet1 and it doesn't contain any data.

Why is Sheet1.UsedRange being weird? Is there a work around?

EDIT: I was using Sheet1.UsedRange in these two ways:

Set rng = Sheet1.UsedRange.Rows(aCell.Row)
For Each C in Sheet1.UsedRange

Is my only hope of reproducing these huge Find statements?

Explanation [Here](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — Siddharth Rout, Oct 30 '14 at 12:33
To get your exact range, find the last row and last column and then construct your range. — Siddharth Rout, Oct 30 '14 at 12:35
Let me know if you are still unsure and then i will give you an example on how to construct your range. — Siddharth Rout, Oct 30 '14 at 13:59
@SiddharthRout #2 was pretty easy to accomplish, but I'm new to VBA and #1 is giving me fits. I just want to be able to extract the `UsedRange` of a known row. Seems like this should be easier than the nasty `Find` I'm cooking up. — Jonathan Mee, Oct 30 '14 at 14:51
@SiddharthRout So I want the row: `aCell.Row` but I only want the `UsedRange` of that row. So in my example I only have data in the first 5 columns. If `aCell.Row` is 2, then I want A2:E2 to be the range. — Jonathan Mee, Oct 30 '14 at 14:56

Siddharth Rout · Accepted Answer · 2014-10-30T17:40:32.560

Further to the comments, is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim lCol As Long, rw As Long

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        rw = 2 '<~~ Known row

        '~~> Find the last column which has data in that row
        lCol = .Cells(rw, .Columns.Count).End(xlToLeft).Column

        '~~> Address of known row which has data
        Debug.Print .Range("A" & rw & ":" & ReturnName(lCol) & rw).Address
    End With
End Sub

'~~> Function to return Column letter from column number
Function ReturnName(ByVal num As Integer) As String
    ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

And if you want to set it as a range then like this

Sub Sample()
    Dim ws As Worksheet
    Dim lCol As Long, rw As Long
    Dim rng As Range
    Dim sAddress As String

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        rw = 2 '<~~ Known row

        '~~> Find the last column which has data in that row
        lCol = .Cells(rw, .Columns.Count).End(xlToLeft).Column

        '~~> Address of known row which has data
        sAddress = .Range("A" & rw & ":" & ReturnName(lCol) & rw).Address

        '~~> Set Range
        Set rng = .Range(sAddress)
    End With
End Sub

'~~> Function to return Column letter from column number
Function ReturnName(ByVal num As Integer) As String
    ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

Followup From Comments

Sub Sample()
    Dim ws As Worksheet
    Dim lCol As Long, rw As Long
    Dim rng As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        rw = 2 '<~~ Known row

        '~~> Find the last column which has data in that row
        lCol = .Cells(rw, .Columns.Count).End(xlToLeft).Column

        '~~> Set Range
        Set rng = .Range(.Cells(rw, 1), .Cells(rw, lCol))

        Debug.Print rng.Address

    End With
End Sub

So my best shot at this is to use string construction? Pardon my ignorance of VBA my native language is C++, and passing around strings to get numbers out would be considered taboo. — Jonathan Mee, Oct 30 '14 at 16:50
That was just an example :) You can do it as `Set rng = .Range("A" & rw & ":" & ReturnName(lCol) & rw)` — Siddharth Rout, Oct 30 '14 at 17:28
Oh, the string construction I was talking about was all the `&`s in that line and the `Split` in `ReturnName`. — Jonathan Mee, Oct 30 '14 at 17:36
For what it's worth the latest uncleanliness I've cooked up is: `Sheet1.Range(Sheet1.Cells(aCell.Row, 1), Sheet1.Rows(aCell.Row).End(xlToRight))` — Jonathan Mee, Oct 30 '14 at 17:40
No :) Don't use `xlToRight`. I have already updated the code. You may want to refresh the page? — Siddharth Rout, Oct 30 '14 at 17:41
Sweet, that's exactly what I want, just for my own understanding, what's the reason that I shouldn't use `xlToRight` vs `xlToLeft`? — Jonathan Mee, Oct 30 '14 at 17:44

score -1 · Answer 2 · answered Oct 30 '14 at 13:24

-1

Excel seems to keep the info about what range already was used, not just the part that is in use right now.

This should do the trick:

Dim oRng as Range
WIth Sheets(1)
    Set oRng = .Range(.Range("A1").End(xlDown), .Range("A1").End(xlToRight))
End With

answered Oct 30 '14 at 13:24

Thiago Cardoso

47
1

1

[This](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) is actually the canonical way to find the last cell (and hence construct a range) in any range :) – David Zemens Oct 30 '14 at 13:26
3

This is the wrong way to find your range. What if there are blank cells in between ? This will also give you undesired results if only A1 has data. You will end up selecting the entire worksheet cells – Siddharth Rout Oct 30 '14 at 13:53

Sheet1.UsedRange Weirdness

2 Answers2