Count cells in row until blank and return Cel #

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
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
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You can use something like this
Rich (BB code):
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
 
Upvote 0
[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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top