Finging first row of a given column that has data in it.......exclude header

[welchs101]

I have this code and it works to find the first row of data in colB where it excludes the header row.

first_data_row = ws.Cells.Find(What:="*", After:=[B1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row

However, when i try to make the code more flexible by makeing B1 a variable .....i get a type mismatch........see below

Dim study_col As Integer

fl_name = ThisWorkbook.Name
Set ws = Workbooks(fl_name).Worksheets("Sheet1")
Set ws3 = Workbooks(fl_name).Worksheets("Sheet3")
study_col = 2
'first_data_row = ws.Cells.Find(What:="*", After:=[B1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
first_data_row = ws.Cells.Find(What:="*", After:=[alphacol(study_col) & "1"], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
MsgBox (first_data_row)

I am enclosing an excel file as this usually makes things easier.

[Mordred]

Hi welchs101, I had troubles with this line

first_data_row = ws.Cells.Find(What:="*", After:=[alphacol(study_col) & "1"], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row

and I think it is because of alphacol. You have to declare alphacol as a range and then set it to B1 first. Let me know if the following does what you want

Sub Macro3()
    Dim study_col As Integer
    Dim first_data_row As Long, alphacol As Range
    fl_name = ThisWorkbook.Name
    Set ws = Workbooks(fl_name).Worksheets("Sheet1")
    Set ws3 = Workbooks(fl_name).Worksheets("Sheet3")
    study_col = 2
    Set alphacol = ws.Cells(1, 2)
    first_data_row = ws.Cells.Find(What:="*", After:=[alphacol], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
    MsgBox (first_data_row)
End Sub

[welchs101]

Yes, that worked perfectly.

But how did you know to convert it to a range.

How did you know what the cells.find format needed?

[Mordred]

I knew that because After:= means after a range/cell (at least that's what I thought it meant).

[welchs101]

good enough for me.....thanks.