Excel VBA - selecting range to last completely blank row

Question

I have some VBA code that needs to select a range from A84 to X. I'm using this code to select that range to the last row in the data.

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A84:X" & Lastrow).Select

This stops at the first row with a blank cell in the A column. I need it to check columns A through Z, not just A. For example, if there is a value in column A on rows 84 to 94, but for row 95, there's only data in column F or R, it won't include row 95. How can this look at columns A:Z to determine whether or not the row is blank?

Answer 1

simply set Lastrow to

Lastrow = Range("A:Z").Find("*", , , , xlByRows, xlPrevious).Row

Answer 2

I'm all but certain there is a better way, but one such way is to loop through you columns and find the last row of each and then determine which one of those is the greatest.

maxRow = 0
for i=1 to 24 'A through X
    if maxRow < ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row then
        maxRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row
    end if
next i

Then maxRow is what you want lastrow to be.

Answer 3

You could iterate through all of the columns and compare the last row of each column. You could then change the lastRow variable whenever you find a column with more rows like so:

Dim i As Integer
Dim lastRow As Integer
lastRow = 1
For i = 1 To 26
    Dim lst As Integer
    lst = Cells(Rows.Count, i).End(xlUp).Row
    If lst > lastRow Then
        lastRow = lst
    End If
Next
Range("A84:X" & lastRow).Select

Answer 4

You can make use of the usedrange function, which returns the complete range of used cells on your sheet:, as follows:

Worksheets("worksheetName").UsedRange.lastRow

This would give you the last used row, not just the last row with data in the first column