CODEWITHSUNDEEP
excelvba
user2902589
user2902589

Reputation: 71

How to select the lastrow

I have an excel sheet it contains data as below:

    A      B      C     D     E      F           
1   10     11     12    78    45

2   12     15     15    78    45

3   17     18     13    7     45

4   12     45     7     78    78

5   578    54     45    8     78

6   42     72     75    8     78

7   452    22252  2277  87    986

8   752    72     752   878   98638

9   72     72     72    45    78

10  788    72     78    678   465

Now, I want to select the last row in this excel. Because everyday the row number will be change.
But I don't want the Selection.End(xlToRight).Select because in this excel blanks will come after every 2 columns.
Ex:

I want to select A10 to last cell in this row.

I have create “usedrange” method but it doesn’t work well for me.

startcell = Range("B" & Cells.Rows.Count).End(xlUp).Select
endrcell = Range("G" & Cells.Rows.Count).End(xlUp).Select

How can I select the startrow to endcell with blanks?

Please advice.

Upvotes: 0

Views: 3559

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

To Select the last row with data, first move upwards from the bottom to find the row and then move leftwards on that row to find that last used column:

Sub SelectLastRow()
    Dim nRow As Long, nColumn As Long
    nRow = Cells(Rows.Count, "A").End(xlUp).Row
    nColumn = Cells(nRow, Columns.Count).End(xlToLeft).Column
    Range(Cells(nRow, "A"), Cells(nRow, nColumn)).Select
End Sub

Upvotes: 1

Alex P
Alex P

Reputation: 12489

How about this:

Sub GetLastRow()
    Dim rng As Range, lastRow As Long, col As Long

    lastRow = Range("A1").End(xlDown).Row //Get last row in column A
    col = Range("XFD" & lastRow).End(xlToLeft).Column //Get last used column in last row

    Set rng = Range(Cells(lastRow, 1), Cells(lastRow, col))
End Sub

Upvotes: 0

Related Questions