Question: I want to select the entire column that contain the names to copy it knowing that the Name column number can change between a spreadsheet and another and there is some blank cells in the middle (missing value)

Suppose that the Names column is the column B, so if I want to select the entire column even with the some blank cells in the middle I can use the following code:

```
Range("B2", Range("B" & Rows.Count).End(xlup)).Select

```

But the Name column number is variable. so i tried to make it like this :

```
Sub ColSelection ()

Dim NameHeader As range 

'To select the header of Name column 

Set NameHeader = ActiveSheet.UsedRange.Find("Name")

ActiveSheet.Range(NameHeader.Offset(1,0), Range(NameHeader & Rows.Count).End(xlUp)).select 

' run tim error 1004 " Methode 'Range' of object '_Global' Failed 

End sub

```

I guess that I have to replace the second NameHeader with his column address. how to do that? Should I set a Var to store the address of the range NameHeader, and use it. If it's the case how should I set this var, I mean as long or as Variant..ect? 

Thanks :)

Answer: 
Is this what you are trying? I have commented the code so you should not have a problem understanding it. But if you still do then post your query.

Is this what you are trying?

```
Sub ColSelection()
    Dim NameHeader As Range
    Dim ws As Worksheet
    Dim ColName As String
    Dim LRow As Long
    Dim rng As Range

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        Set NameHeader = .UsedRange.Find("Name")

        '~~> Check if we found the text
        If Not NameHeader Is Nothing Then
            '~~> Get the Column Name
            ColName = Split(Cells(, NameHeader.Column).Address, "$")(1)

            '~~> Get the last row in that range
            LRow = .Range(ColName & .Rows.Count).End(xlUp).Row

            '~~> Construct your range
            Set rng = .Range(ColName & NameHeader.Row & ":" & ColName & LRow)

            With rng
                MsgBox .Address
                '~~> Do whatever you want with the range
            End With
        End If
    End With
End Sub

```

**Screenshot**

Unable to upload an image, Imgur is rejecting images for the time being. Will update it directly later.

<http://prntscr.com/kftsad>
Sub test1a()

```
Dim NameHeader As Range
Set NameHeader = ActiveSheet.UsedRange.Find(InputBox("HEADER"))
If Not NameHeader Is Nothing Then
ActiveSheet.Range(NameHeader.Offset(1), Cells(Rows.Count, NameHeader.Column).End(xlUp)).Select
End If

```

End Sub