How to set a column as a range variable as long as there is data in it

Question

I would like excel to input the entire column with the title "Section" into the range variable 'sectioncol'.

Set sectioncol = range(range("A1:R1").Find(what:="Section", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Address & ":" & range("A1:R1").Find(what:="Section", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).End(xlDown).Address)

I was expecting the range variable to return the cells containing data under the section column.

Answer 1

Try this:

Dim rng As Range
Set rng = Range("A1:R1").Find( _
    What:="Section", LookIn:=xlValues, _
    LookAt:=xlWhole, MatchCase:=False)

If Not rng Is Nothing Then ' Test if Section was found
    Dim sectioncol As Range
    Set sectioncol = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
End If