How to select the lastrow

Question

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.

possible duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) — brettdj, Dec 23 '13 at 10:54

Gary's Student · Answer 1 · 2013-12-23T14:21:54.690

1

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

edited Dec 23 '13 at 14:21

answered Dec 23 '13 at 14:11

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Dec 23 '13 at 10:52

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

How to select the lastrow

2 Answers2

Linked