Selecting entire column after searching for header

Question

Trying to teach myself VBA and am very new to this. Stackoverflow has been an awesome resource so far so I figured I'd ask it here.

What I want it to do is find the cell named "Reference," then copy the column below it.

For some reason I can't find this anywhere online and figured I'd bite the bullet and submit a question.

Thanks.

Answer 1

It depends on what you mean by "cell named". If the cell is in fact named "Reference" (i.e. if you have renamed the range of cell A1 to Reference, you can then copy the cell below this by using the offset method:

ActiveSheet.Range("Reference").Offset(1, 0).Copy

If you instead are searching for a cell with the value "Reference" you can find this cell and copy the cell blow this by using a method like this (note that if there are multiple cells that meet the search criteria, it will copy the cell below the last found cell):

With ActiveSheet.UsedRange
    Set c = .Find("Reference", LookIn:=xlValues)
    If Not c Is Nothing Then
        ActiveSheet.Range(c.Address).Offset(1, 0).Copy
    End If
End With

If you want to copy an entire column below a specific cell, you can do something in line of the following code. In this case it will copy all of used cells below the cell C7.

Sub CopyColumnBelow()
    Dim r As Range
    Set r = ActiveSheet.Range("C7")

    ActiveSheet.Range(r, ActiveSheet.Cells(Rows.Count, r.Column).End(xlUp).Address).Copy
End Sub

Hope you can use this to move forward learning Excel VBA.