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: 
No need to look at `NameHeader.Address` - just use its `.Column` - something like this (noting that you don't have to `Select` a `Range` before copying.

```
Sub GrabNameCol()
    Dim NameHeader As Range
    Set NameHeader = ActiveSheet.UsedRange.Find("Name")

    If Not NameHeader Is Nothing Then
        ActiveSheet.Range(NameHeader.Offset(1), Cells(Rows.Count, NameHeader.Column).End(xlUp)).Select ' or just .Copy
    End If

End Sub

```
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