Counting number of rows including blank rows until 2 blanks encountered

Question

Currently have a macro which counts the number of rows to use as a variable. Due to new data source which has blank rows this no longer functions.

I need it to continue counting until it hits two blanks which is the end of the data source but also include the blank rows in the count.

I have a macro that counts the number of rows to provide a variable for a separate macro which uses that number for a loop function. Everything was working fine except the new data to count has blank row in between data (which must remain and included in the total row count).

I can figure out how to count non-blanks and full cells separately but can't figure out how to do it together. Any suggestions?

Sub num_rows(nrows As Variant)
    Dim numrows
    Dim ra As Range
    Dim i As Integer

    'get number of rows between blank cells
    Sheets("4 Gantt Overview").Activate
    Set ra = Range("b7")

    numrows = Range(ra.Address,Range(ra.Address).End(xlDown)).rows.Count
    Range(ra.Address).Select

    'establish counting loop
    For i = 1 To numrows      
        ActiveCell.Offset(1, 0).Select          
    Next

    nrows = numrows        
    Range("b7").Select 
End Sub

For a data set of 130 rows and 2 blanks its counting only to 30 rows (the first blank position).

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 22 '19 at 08:35
`For a data set of 130 rows and 2 blanks its counting only to 30 rows (the first blank position).` That is because you are using `xlDown` I think what you need is the last row? If yes then you may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Siddharth Rout, Jan 22 '19 at 09:01
Is this your range? `MsgBox Sheets("4 Gantt Overview").Range("B7:B" & Sheets("4 Gantt Overview").Range("B" & Rows.count).End(xlUp).Row).Address` — Siddharth Rout, Jan 22 '19 at 09:05

Pᴇʜ · Accepted Answer · 2019-01-22T09:01:05.413

Imagine the following data:

If you want to find the first 2 blanks, you can use .SpecialCells(xlCellTypeBlanks) to fund all blanks in your range (here column A). It will turn something like the selected cells in the image. There are 6 selected areas that you can access with .SpecialCells(xlCellTypeBlanks).Areas.

So if we loop through all these areas For Each Area In .Areas and check their row count If Area.Rows.Count >= 2, we can easily find the area with 2 rows (or at least 2 rows).

The amount of rows (empty or not) is then Area.Row - AnalyzeRange.Row

So we end up with:

Option Explicit

Sub TestCount()
    MsgBox CountRowsUntilTwoBlanks(Worksheets("Sheet1").Range("A:A"))
End Sub


Function CountRowsUntilTwoBlanks(AnalyzeRange As Range) As Long
    Dim Area As Range
    For Each Area In AnalyzeRange.SpecialCells(xlCellTypeBlanks).Areas
        If Area.Rows.Count >= 2 Then 'if 2 or more then use >=2, if exactly 2 use =2
            CountRowsUntilTwoBlanks = Area.Row - AnalyzeRange.Row
            Exit For
        End If
    Next Area
End Function

So for this example it will return 16 rows.

Note that if your goal is to find the last used row, which in this example would be row 20 then you could just use …

Dim LastRow As Long
With Worksheets("Sheet1")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

… to find the last used row in column A. Here LastRow returns 20.

Perfect! With a slight rejig that got everything working again. Didn't think of the .SpecialCells(xlCellTypeBlanks).Areas function - Thanks mate. — user10948393, Jan 22 '19 at 22:31

score 0 · Answer 2 · answered Jan 22 '19 at 06:29

This this macro. It will find first cell that is blank with a following cell blank as well.

Sub stopAtDoubleBlank()

        Dim i As Long

        i = 2
        Do While Range("A" & i).Value <> "" Or Range("A" & i + 1) <> ""

            i = i + 1
        Loop

        MsgBox i

End Sub

score 0 · Answer 3 · answered Jan 22 '19 at 08:39

0

You can try something like this too if you want:

Sub lastrow()
    Dim lr As Long
    lr = ActiveSheet.Rows.Count
    Cells(1, lr).Select
    Selection.End(xlUp).Select
    lr = ActiveCell.Row
End Sub

(go down to the very bottom and jump up to the last not empty row in A cloumn(that can be changed) also you can add something like +1 if you want an empty row at the end)

answered Jan 22 '19 at 08:39

Peter

16
4

1

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Pᴇʜ Jan 22 '19 at 08:49
Thank you for the article!:) – Peter Jan 22 '19 at 09:16

Counting number of rows including blank rows until 2 blanks encountered

3 Answers3