?
Solved

Dynamically count blanks in Excel

Posted on 2011-10-05
6
Medium Priority
?
263 Views
Last Modified: 2012-05-12
Hi there,
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... 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 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

Open in new window

0
Comment
Question by:Classic1
5 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 36919209
it might be helpful to show us generateHTML1-6 code
0
 
LVL 14

Expert Comment

by:John-Charles-Herzberg
ID: 36919296
This might help.

Assume Range A1:B15 (14 cells out of the 30 contain data)

Use the following function: =COUNTBLANK(A1:B15)
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36919367
Perhaps this?
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

Open in new window

0
 

Author Comment

by:Classic1
ID: 36949147
Hey all,
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
0
 

Author Closing Comment

by:Classic1
ID: 36979844
Hi StephenJR,
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
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question