Select all cells with a value under header

Question

I have a code that selects the column which has "Address" header.

Sub FindAddressColumn()
  Dim rngAddress As Range
  Set rngAddress = Range("A1:Z1").Find("Address")
  If rngAddress Is Nothing Then
    MsgBox "Address column was not found."
    Exit Sub
  End If
  Range(rngAddress, rngAddress.End(xlDown)).Select
End Sub

I want to select 2nd row or the row after the header "Address" all the way down to the last row with value.

`Range(rngAddress, rngAddress.End(xlDown)).Offset(,1).Select` — Scott Craner, Mar 09 '16 at 21:15
I edited my question, what I mean select the next row after the header "Address". Sorry for the confusion. — Paolo Medina, Mar 09 '16 at 21:19
`Range(rngAddress.offset(1), rngAddress.End(xlDown)).Select` — Scott Craner, Mar 09 '16 at 21:20
Thank you so much! What is the difference of that line and this line: `Range(rngAddress.Cells(2, 1), rngAddress.End(xlDown).Cells(2, 1)).Select` — Paolo Medina, Mar 09 '16 at 21:24
Not much. The `.Cells(2,1)` when referring to a single cell does the same as offset(1). I would not offset after the `End(xlDown)` you are no including an extra empty cell on the bottom. As you have it written `Range(rngAddress, rngAddress.End(xlDown)).Offset(1).Select` would give you the same range without extra typing. — Scott Craner, Mar 09 '16 at 21:28

score 2 · Answer 1 · edited May 23 '17 at 11:53

Use the WorksheetFunction object MATCH function to locate the column header label in the first row.

Sub FindAddressColumn()
    dim cl as long
    with worksheets("Sheet1")
        if not iserror(application.match("address", .rows(1), 0)) then
            cl = application.match("address", .rows(1), 0)
            .range(.cells(2, cl), .cells(rows.count, cl).end(xlup)).select
        else
            MsgBox "Address column was not found."
            Exit Sub
        end if
    end with
End Sub

I prefer to look from the bottom up to find the last populated cell in a column but if you prefer to look from the top down, the above code could be easily modified to suit.

The Range .Select¹ method is not always the most reliable method of referencing individual or groups of Range objects. Directly referencing the parent worksheet with a With ... End With statement should help.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

score 2 · Answer 2 · edited Feb 17 '17 at 18:19

In case you have blanks in your address column, use this code:

Sub FindAddressColumn()
  Dim rngAddress As Range, LastRow As Long
  Set rngAddress = Range("A1:Z1").Find("Address")
  If rngAddress Is Nothing Then
    MsgBox "Address column was not found."
  Else
    LastRow = Cells(Rows.Count, rngAddress.Column).End(xlUp).Row
    Range(Cells(2, rngAddress.Column), Cells(LastRow, rngAddress.Column)).Select
  End If
End Sub

Select all cells with a value under header

2 Answers2