troubleshooting Question

Dynamically count blanks in Excel

Avatar of Classic1
Classic1Flag for Canada asked on
Microsoft ExcelVB Script
5 Comments1 Solution321 ViewsLast Modified:
Hi there,
I was wondering if anyone can help me out collegue stopped by and asked if there's a way to count the # of blanks after a populated row, jump out of the loop, do another subroutine, then jump back in to the loop again... Screenshot example
So what he would like to do is (in the screenshot example):
See 09-B0640, count the 1 blank, exit out of the loop, based on the # of blanks (this case 1), go throught the Case function...after that's processed, go on to 09-P0650, count the blanks, exit out to the Case function, etc...etc...

I've also posted his code...I tried editing it, and I think there's got to be a Do While loop there or something...but I didn't want butcher his code and all the time he spent on

If you can help us out, that would be awesome!!!


Dim ALastRow As Long
Dim RowCount As Long
Dim BlankCount As Long

ALastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

    For RowCount = 11 To ALastRow
        If Range("A" & RowCount) <> "" Then 'If cell is not blank
            BlankCount = 1
            Range("C" & RowCount) = BlankCount
            BlankCount = 1
        Else 'If cell is blank
            BlankCount = BlankCount + 1
            Range("C" & RowCount - BlankCount + 1) = BlankCount
        End If
        Debug.Print BlankCount
        If BlankCount - 1 < 1 Then
            MsgBox "The Do Until loop made is complete."
        End If
        Exit For
    Next 'RowCount

    Select Case BlankCount
        Case 1
        Case 2
        Case 3
        Case 4
        Case 5
        Case 6
    End Select

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros