Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Blank Rows (2000)-MULTI-Counting Blank Rows (20

    I have been trying to write some code to count the number of empty rows on a worksheet. I located this (presumably) legacy code (designed to delete empty rows), but the CountA property seems to have no counterpart in Excel 2000, at least not that I can find:

    LastRow = ActiveSheet.UsedRange.Row - 1 + _
    ActiveSheet.UsedRange.Rows.Count ' don't understand this bit ???
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
    If Application.CountA(Rows®) = 0 Then Rows®.Delete
    Next r

    I'm guessing I have to start looping through a range container, but I'm generally at a loss. Any suggestions please?

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Blank Rows (2000)-MULTI-Counting Blank Rows (20

    Something like this?

    Sub test()
    Dim lTotal As Long
    On Error Resume Next
    lTotal = ActiveSheet.[a:a].SpecialCells(xlCellTypeFormulas).Count
    lTotal = lTotal + ActiveSheet.[a:a].SpecialCells(xlCellTypeConstants).Count
    lTotal = ActiveSheet.[a:a].Cells.Count - lTotal
    MsgBox lTotal
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Blank Rows (2000)-MULTI-Counting Blank Rows (20

    When all those properties get slung together it gets confusing! This little demo macro will hopefully answer some of your questions, but feel free to ask more. First try it with no empty rows at the top, then insert some blank rows. Note, each dot in this sample is essential, so you should copy it instead of retyping it. First select the code, then paste it into word, then copy it from Word, and paste it into a code module. That gets rid of HTML formatting. HTH --Sam
    <pre>Option Explicit
    Sub Counts()
    Dim i As Long
    Dim iEmpty As Long
    With ActiveSheet.UsedRange
    MsgBox "First row " & .Row
    MsgBox "Number of used rows " & .Rows.Count
    MsgBox "Last row " & .Row + .Rows.Count - 1
    MsgBox "Column B, empty cells " & _
    Application.WorksheetFunction.CountBlank( _
    Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(2)))
    iEmpty = 0
    For i = 1 To .Rows.Count
    If Application.WorksheetFunction.CountA(.Rows(i)) = 0 Then _
    iEmpty = iEmpty + 1
    Next i
    MsgBox "Empty rows in used range " & iEmpty
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Blank Rows (2000)-MULTI-Counting Blank Rows (20

    Thank you Jan (once again). I would never have "stumbled" on these functions & properties myself. Each time I think I've "grasped it", I discover whole new worlds out there. Still, the learning process is rewarding, if not a little frustrating [img]/forums/images/smilies/smile.gif[/img] at times.

    cheers

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Blank Rows (2000)-MULTI-Counting Blank Rows (20

    Thank you Sam.
    Your code looks both educational and useful. I look forward to trying/playing with it.

    thank you

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Blank Rows (2000)-MULTI-Counting Blank Rows

    Now that I'm on "a roll" with this, I'm trying to adapt these methods to do something like this:

    Check to see if the row is blank.
    If yes, increment BlankRowCount
    If no,
    check to see if col 3 of that row is blank, and append that row number to a string called BlankCol3

    Present the whole summary information in a message box, similar to the way it's done in previous post.

    I'll no doubt scream if I get stuck. [img]/forums/images/smilies/smile.gif[/img]

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •