'VBA Loop count non-blank cells in Column A to next blank, put that count total in column D same row as blank in Column A, zero and repeat to last B
I need the count of non-blank cells in Column A down to the next blank and putting the count in Column D on same row as the Column A blank cell found, reset the count and looping through to the last used column B non-blank cell. I can't use sheet formulas due to the type of data import unless I use VBA to add the formula then convert to values. That is pretty messy. Here is the code I tested that totals the value of the non-blank cells and puts that total in the correct place along with coloring the cell, which I have disabled. I need it to total the number of non-blank cells not their values, reset the count and continue down Column A. TIA for any help,
I need the count of non-blank cells in Column A down to the next blank and putting the count in Column D on same row as the Column A blank cell found, reset the count and looping through to the last used column B non-blank cell. I can't use sheet formulas due to the type of data import unless I use VBA to add the formula then convert to values. That is pretty messy. Here is the code I tested that totals the value of the non-blank cells and puts that total in the correct place along with coloring the cell, which I have disabled. I need it to total the number of non-blank cells not their values, reset the count and continue down Column A.
TIA for any help,
Sub Count_Stops()
'Works for Summing Non-Blank cell Values
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
firstrow = 2
TempTotal = 0
For x = firstrow To lastrow + 1
If Cells(x, "A") <> "" Then
TempTotal = TempTotal + Cells(x, "A")
Else: Cells(x, "D") = TempTotal
'Cells(x, "D").Interior.ColorIndex = 4 'Disabled for now
TempTotal = 0
End If
Next x
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
