VBA Code to Count and Total the Number of Blank Rows

[Eshleman]

Hi.

I'm looking for VBA code that counts and totals the number of nonblank rows between data in column F of my spreadsheet. The issue that I am having is that the number of rows is not consistent. For example, the data looks like this:



F1 $1,200
F2 blank
F3 $128
F4 blank
F5 $105
F6 $100
F7 $200
F8 blank

The spreadsheet is very large. The above data is just an example. So, in column G, I need to see a 1 in cell G1. I need to see a 1 in G3. And, I need to see a 3 in cell G5. For cells that are blank in column F, the respective cells in column G should also be blank.

 

[skywriter]

Here's the code and the results of running the code.

Sub Eshleman()
Dim j
    For Each j In Range("F1", Range("F" & Rows.Count).End(xlUp)).SpecialCells(2).Areas
        j(1, 2).Value = j.Rows.Count
    Next j
End Sub

Excel Workbook
	F	G
1	$1,200	1
2
3	$128	1
4
5	$105	3
6	$100
7	$200
Sheet1

 

[Eshleman]

Thank you, skywriter. This code is exactly what I need. I really appreciate your help!

 

[skywriter]

My pleasure, thanks for the feedback.