Count cells in row until blank and return Cel #

[Jimmy P]

I want to count the number of cells in a row until a blank is reached and then return the number of cells to the left of the first blank cell to a worksheet cell and post a message box with the count.

A A A A A
1 2 3 4 5

1 2 3 4 blank cell

Result should be = 4

I found a Sub here and modified it but get compile errors. "Object doesn't support this property or method"
I'm sure it's simple, but beyond me right now.

Sub CountToLastC()
'
Sheets("Samples").Select

range("A1").Resize(Rows("1").Find(What:="*", After:=range("A1"), LookIn:=ylValues, SearchDirection:=ylPrevious).col).Select

End Sub

 

[Momentman]

You can use something like this

Sub findblank()
    MsgBox Cells(1, 1).End(xlToRight).Column 'Change the number in red to reflect the row you want to run the code on
End Sub

 

[Jimmy P]

[You can use something like this

Sub findblank()
MsgBox Cells(1, 1).End(xlToRight).Column 'Change the number in red to reflect the row you want to run the code on
End Sub


That works great. How do I pass the results to a worksheet cell?

 

[Jimmy P]

Figured it out. Thanks for your help.

Sub CountToLastCol()
'
Sheets("Samples").Select

Cnt = Cells(1, 1).End(xlToRight).Column
Sheets("Calc").range("D2") = Cnt
MsgBox "There are " & Cnt & " Total Samples" & vbNewLine & "Click OK to Continue", vbONOnly, "Sample Count"

End Sub