VBA count Blank Cells

[Finalfight40]

Hi All

I am looking for a little advice as i expect i am going to get a spreadsheet soon with exported data and i wanted to get a head start on a piece of VBA that i'm not aware how to do:

So i have the example data below as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]######[/TD]
[TD]######[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]####[/TD]
[TD]########[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]####[/TD]
[TD]#####[/TD]
[TD]####[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jamie[/TD]
[TD]###[/TD]
[TD]#####[/TD]
[TD]#######[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]#####[/TD]
[TD]######[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bob[/TD]
[TD]####[/TD]
[TD]#####[/TD]
[TD]#####[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Polly[/TD]
[TD]####[/TD]
[TD]######[/TD]
[TD]#####[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]#######[/TD]
[TD]####[/TD]
[TD]#######[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]######[/TD]
[TD]####[/TD]
[TD]###[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]###[/TD]
[TD]######[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]####[/TD]
[TD]#######[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Jeff[/TD]
[TD]####[/TD]
[TD]######[/TD]
[TD]######[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]######[/TD]
[TD]####[/TD]
[TD]######[/TD]
[/TR]
</tbody>[/TABLE]

So there would be 1 thing i was hoping to get advice on.

Lets say my active cell is currently on John. How could i determine the number of blank cells below John in Column A before i reached Jamie. (So for this example 2)

Also this would need to work for an example like Bob where it would return 0 blank cells.

 

[PhilS2520]

Hi Finalfight40,

You do not need VBA to count blank cells. Yes it can be done with VBA, but I suggest using worksheet functions when they are properly needed, and VBA for other automation needs. And a combination of worksheet functions and VBA at times.

For example: to find if a cell is blank, you can use the LEN() function to check if what is in it has a zero length thus it will be empty.

Now knowing what you want to do with these blank cells, other than counting them, will help in developing a more productive reply.

Hope this helps.

 

[Scott T]

Mayble

Sub countcells()
Dim lr As Long
Dim nr As Long
Dim cr As Long
If ActiveCell = "" Then
    MsgBox ("No name selected")
    Exit Sub
End If
lr = Cells(Rows.Count, "A").End(xlUp).Row
cr = ActiveCell.Row
nr = ActiveCell.End(xlDown).Row
If nr <= lr Then
    countc = nr - cr - 1
    MsgBox ("The number of blank cells between names is " & countc)
Else
    MsgBox ("Selected name is the last name no next name")
End If

 

[Finalfight40]

Hi Phil

Unfortunately this would need to be done via VBA because it will be put into a macro to perform various other calculations.

 

[Finalfight40]

Scott

That works perfectly and is exactly what i am looking for.

Thank you