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:

	A	B	C	D
1	Name	Date	Time In	Time Out
2	John	######	######	######
3		####	########	######
4		####	#####	####
5	Jamie	###	#####	#######
6		#####	######	######
7	Bob	####	#####	#####
8	Polly	####	######	#####
9		#######	####	#######
10		######	####	###
11		###	######	######
12		####	#######	######
13	Jeff	####	######	######
14		######	####	######

<tbody>
</tbody>

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