Finding last row in selected numbers of columns, can't use end(xldown)

Question

I have a set of columns ( column : "C:I" on the excel sheet ) which I want to select the last cells of. The issue is that I can't use xldown because there are some blank cells in the beginning of the columns...

How to effectively select the last row anyway? I'¨m trying Range("C1:I1").rows.end(xldown) but it gets stuck right at the beginning at the last non-empty cell after the empty ones.

possible duplicate of [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — RubberDuck, Aug 23 '14 at 22:34

score 2 · Answer 1 · answered Aug 23 '14 at 13:27

2

An example which works on a single column:

Sub findLastCellInColumnA()

Dim lstRow As Integer
lstRow = Cells(Rows.Count, 1).End(Excel.xlUp).Row
Cells(lstRow, 1).Select

End Sub

answered Aug 23 '14 at 13:27

whytheq

34,466
65
172
267

The issue is that I don't want to have the entire row but only the values for column C to I, thus not for column A and B – uncool Aug 23 '14 at 13:39

score 2 · Accepted Answer · answered Aug 23 '14 at 13:28

If the columns are filled in an irregular fashion, then:

Sub qwerty()
    Dim N As Long, M As Long, mm As Long
    M = 0
    For N = 3 To 9
        mm = Cells(Rows.Count, N).End(xlUp).Row
        If mm > M Then
            M = mm
        End If
    Next N
    Cells(M, 1).EntireRow.Select
End Sub

For example:

see

Note that the attractive:

Sub dural()
    Range("C:I").Cells.SpecialCells(xlCellTypeLastCell).Select
    Selection.EntireRow.Select
End Sub

Will select row #18 by mistake!

+1 bob on. I was about to attempt the required loop but beaten to it! — whytheq, Aug 23 '14 at 13:30

Finding last row in selected numbers of columns, can't use end(xldown)

2 Answers2