Dynamically count blanks in Excel
I was wondering if anyone can help me out here...my 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...
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 it...lol.
If you can help us out, that would be awesome!!!
Thanks,
Classic
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
generateHTML1
Case 2
generateHTML2
Case 3
generateHTML3
Case 4
generateHTML4
Case 5
generateHTML5
Case 6
generateHTML6
End Select
Who is Participating?
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Assume Range A1:B15 (14 cells out of the 30 contain data)
Use the following function: =COUNTBLANK(A1:B15)
Sub x()
Dim ALastRow As Long
Dim RowCount As Long
Dim BlankCount As Long
Dim n As Long
ALastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
With Range("A11", Range("A" & ALastRow)).SpecialCells(xlCellTypeConstants)
For n = 1 To .Areas.Count - 1
BlankCount = .Areas(n + 1).Rows(1).Row - .Areas(n).Rows(.Areas(n).Rows.Count).Row - 1
Select Case BlankCount
Case 1
generateHTML1
Case 2
generateHTML2
Case 3
generateHTML3
Case 4
generateHTML4
Case 5
generateHTML5
Case 6
generateHTML6
End Select
Next n
End With
End Sub
Experts Exchange Solution brought to you by
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Sorry for the non-response, I was out of the offiice the latter part of the week, and it was a holiday yesterday...
Thanks for your input, I've relayed the code to my co-worker, so hopefully, I'll get some feedback later today!!!
Much appreciated,
Classic
Sorry for the delay, I gave my co-worker 2 weeks or so to give me some feedback, and he hasn't gotten back to me...but looking @ the code itself, it looks like it does the trick...
Much appreciated,
Classic
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
From novice to tech pro — start learning today.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by