Ufdjrw

Here is the function that can identify a column by its header:

Function find_Col(header As String) As Range

 Dim aCell As Range, rng As Range
 Dim col As Long, lRow As Long
 Dim colName As String
 Dim y As Workbook
 Dim ws1 As Worksheet

 Set y = Workbooks("Template.xlsm")

 Set ws1 = y.Sheets("Results")

 With ws1

 Set aCell = Cells.Find(what:=header, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

 col = aCell.Column
 colName = Split(.Cells(, col).Address, "$")(1)

 lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

 Set myCol = Range(colName & "2")

 'This is your range
 Set find_Col = Range(myCol.Address & ":" & colName & lRow)

 find_Col.Select

 End With

End Function

Then I call the function in the sub:

Sub myCol_Find()

 find_Col ("Product")

End Sub

The above works fine, but the problem I am facing is that if the column I am searching for is pretty much blank, excluding the header, then my function will only select the first 2 rows under the header. Also, a second issue, it selects the row just after the last row as well. So if the first row under the heading is B3 and the last row is B10, it selects B3:B11.

As a result of this, I thought that it would perhaps work better by first identifying a column with data in it (which I know will always have data in it) then use this column to find the last row of data, and finally use the actual column I need for selection.

So I first did a test by changing this line:

 lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

To this:

 lRow = Range("A" & .Rows.count).End(xlUp).Row + 1

And this selected all the cells in my searched for column based on the total rows found in Column A.

Then I thought instead of specifically naming a column, I'll apply the same logic of "finding" a column to find "Column A". So I have this:

Function find_Col(header As String) As Range

 Dim aCell As Range, rng As Range, def_Header As Range
 Dim col As Long, lRow As Long, defCol As Long
 Dim colName As String, defColName As String
 Dim y As Workbook
 Dim ws1 As Worksheet

 Set y = Workbooks("Template.xlsm")

 Set ws1 = y.Sheets("Results")

 With ws1

 Set def_Header = Cells.Find(what:="ID", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

 defCol = def_Header.Column
 defColName = Split(.Cells(, def_Col).Address, "$")(1)

 Set aCell = Cells.Find(what:=header, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

 col = aCell.Column
 colName = Split(.Cells(, col).Address, "$")(1)

' lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

 lRow = Range(defColName & .Rows.count).End(xlUp).Row + 1

 Set myCol = Range(colName & "2")

 'This is your range
 Set find_Col = Range(myCol.Address & ":" & colName & lRow)

 find_Col.Select

 End With

End Function

Extra code added:

Dim def_Header As Range
Dim defCol As Long
Dim defColName As String

Set def_Header = Cells.Find(what:="KW_ID", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

defCol = def_Header.Column
defColName = Split(.Cells(, defCol).Address, "$")(1)

And changed this:

lRow = Range("A" & .Rows.count).End(xlUp).Row + 1

To this:

lRow = Range(defColName & .Rows.count).End(xlUp).Row + 1

Now, I am getting an error at the line:

defCol = def_Header.Column

Error:

Object variable of With block variable not set

I don't quite know what the problem is as it didn't give me this error previously when I defined aCell.

So currently, I am facing two issues:

1. Selection is selecting one extra cell than what is needed


2. I don't know why I am receiving the above error

This should work:

EDIT: updated to deal with cases where the header is found but there is no data

Function find_Col(header As String) As Range

 Dim aCell As Range, bCell As Range, rng As Range

 With Workbooks("Template.xlsm").Sheets("Results")

 Set aCell = .Cells.Find(what:=header, LookIn:=xlValues, lookat:=xlWhole, _
 MatchCase:=False, SearchFormat:=False)

 If Not aCell Is Nothing Then
 Set aCell = aCell.Offset(1, 0)
 Set bCell = .Cells(.Rows.Count, aCell.Column).End(xlUp)
 If bCell.Row > aCell.Row Then
 Set rng = .Range(aCell, bCell) 'column has some content
 Else
 Set rng = aCell 'or nothing? 'column has no content...
 End If
 End If 
 End With

 Set find_col = rng
End Function