First Empty Data row skipping x blanks in find?

[TTom]

To Find First empty row in column:
FirstEmptyRow = Range("A65000").End(xlUp).Row + 1

Is there a way to write code to find first empty only when two, or more empty rows in in sequence are found? (# row to be specified)

Purpose: I often have a sheet that has a blank row to space things in layout as in pivot table reports, sometimes it two or more rows, but I want to find where the skip is greater than that I may specify such as if three empty rows are found in sequence then the first one of these is my end of data plus one.

I could do this with a loop that advances until offset of x rows is empty, but this eats up time in running procedure and I'm looking for more efficient way to write code...

 

[mirabeau]

Could you clarify what you mean by "first empty row"?

From your context it seems you refer only to empty cells in column A, rather than the entire row.

So, if you have say A1 and A2 non-empty, then A3 empty, then A4 and A5 nonempty, which is your "first" non empty row?

Since your context seems to envisage looking from the bottom up, it seems you'll generally have plenty of consecutive empty cells below this, and no consecutives immediately above.

So, it seems your question as worded provides its own answer. As given by your opening expression

FirstEmptyRow = Range("A65000").End(xlUp).Row + 1

which actually is better written as

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

Thus, could you please clarify?

 

[SydneyGeek]

By creating a couple of formulas in a blank column (and then clearing the formulas) you can find the first blank, given a set spacing that classifies as "normal".

Example: I used column H as the blank column for writing the formulas. Adjust as required.

Sub Macro1()
    Dim intBlanks As Integer
    Dim rwLast As Long
    Dim rwFirstBlank
    
    rwLast = Range("A" & Rows.Count).End(xlUp).Row
    intBlanks = 2 'any consecutive blanks GREATER THAN this will be regarded as "blank"
    
    Range("H" & intBlanks + 1 & ":H" & rwLast).FormulaR1C1 = _
        "=IF(AND(ISBLANK(RC1),ISBLANK(R[-1]C1),ISBLANK(R[-2]C1)),1,0)"
    rwFirstBlank = WorksheetFunction.Match(1, Range("H:H"), 0)
    Range("H:H").ClearContents
    
    Debug.Print rwFirstBlank
End Sub

Denis

 

[Peter_SSs]

Denis

I think my guess at what the OP wants is the same as yours but I think yours will easily fail. You have used intBlanks to ensure your formulas do not start in too early in the column, but your formula itself is hard-coded for intBlanks = 2 only.

To demonstrate, run your code (as is) on my data below. It returns 11, as does mine. However, if you chnage intBlanks to say 5, it still returns 11. If you change intBlanks to 1 the code errors.

My attempt is below. It returns 11, being the first row where there is a blank following 2 (as set by myBlanks) blanks.

Excel Workbook
	A
1	x
2
3
4	x
5	x
6
7	x
8	x
9
10
11
12
13	x
14	x
15	x
16
17
18
19	x
20	x
21
Sheet2

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> TTom()<br>    <SPAN style="color:#00007F">Dim</SPAN> BlAreas <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myBlanks <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2 <SPAN style="color:#007F00">'<- No. of blanks to ignore</SPAN><br>    <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> BlAreas = Columns("A").SpecialCells(xlCellTypeBlanks)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> BlAreas <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Do</SPAN><br>            i = i + 1<br>            <SPAN style="color:#00007F">With</SPAN> BlAreas.Areas(i)<br>                <SPAN style="color:#00007F">If</SPAN> .Rows.Count > myBlanks <SPAN style="color:#00007F">Then</SPAN> myRow = .Row + myBlanks<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> myRow = 0 And i < BlAreas.Areas.Count<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> myRow = 0 <SPAN style="color:#00007F">Then</SPAN><br>        myRow = Range("A" & Rows.Count).End(xlUp).Row + myBlanks + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    MsgBox "First blank after " & myBlanks & " blanks is row " & myRow<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[SydneyGeek]

Hi Peter,

Yes -- I see the issue you mentioned.
I like the use of Areas; that hadn't occured to me.
Yours looks like a more robust solution

Denis

 

[mirabeau]

... being the first row where there is a blank following 2 (as set by myBlanks) blanks.

If this is the valid interpretation of the opening post then you might try this code

Sub testcode()
Dim e As Range
Set e = Range("A1")
Do
If (e.End(4).Row - e.Row > 3) * (e(4) = "") Then
    e(4).Select
    Exit Do
End If
Set e = e.End(4)
Loop Until e.Row = Rows.Count
End Sub

Incidentally, I don't see Peter's code as being particularly robust, since it may fail (without warning), as when his

Columns("A").SpecialCells(xlCellTypeBlanks)

contains more than some number of blanks.

 

[Peter_SSs]

Incidentally, I don't see Peter's code as being particularly robust, since it may fail (without warning), as when his

Columns("A").SpecialCells(xlCellTypeBlanks)

contains more than some number of blanks.

You are correct. After posting I did some more testing with larger data sets and it did fail when I had many rows of data with many blanks. I'm not sure where the limit is reached (is it 8,192 areas?) but with 1,000,000 rows filled as follows it certainly failed.
x

x

x

In that circumstance, I found this to be very fast:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> TTom2()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">Const</SPAN> myBlanks <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2 <SPAN style="color:#007F00">'<- No. of blanks to ignore</SPAN><br>    LR = Range("A" & Rows.Count).End(xlUp).Row + myBlanks + 1<br>    Data = Range("A1:A" & LR).Value<br>    <SPAN style="color:#00007F">Do</SPAN><br>        i = i + 1<br>        c = IIf(IsEmpty(Data(i, 1)), c + 1, 0)<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> c = myBlanks + 1<br>    MsgBox "First blank after " & myBlanks & " blanks is row " & i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[mirabeau]

... with many blanks. I'm not sure where the limit is reached (is it 8,192 areas?)

In that circumstance, I found this to be very fast: ...

My understanding is that SpecialCells in general don't work (well?) with more than 8192 contiguous areas. And in my experience they don't work very well - often not at all - if there's more than about 37,000 in total, even if not contiguous.

Regarding your second comment quoted above, relating to your more recent code, suppose with the dataset you described, and with the first 3 empty cells in A10, A11 and A12, do you really find your code to be "very fast"?

 

[Peter_SSs]

... with the dataset you described, and with the first 3 empty cells in A10, A11 and A12, do you really find your code to be "very fast"?

"Very fast" of couse is subjective.

My 'in that circumstance' comment was in relation to the data set I described in that post. 'In that circumstance' my code took 0.375 seconds, yours took 4.164 seconds.

Given the data set you suggest to try it on, my code took 0.094 seconds whilst yours didn't trouble the timer I used at all - instantaneous. However, if I only have to wait less than a tenth of a second to produce a result from a million rows of data, my answer is still 'yes, very fast'.

Having said that, unless the data set is *large* either code is going to be 'very fast' in my mind and should suit the OP (provided the requirement interpretation has been correct). I must also say that I am not trying to create a competition and that I found the logic behind your code great, I don't think I would have thought of that approach. Hopefully I will be able to recall it in the future.

 

[TTom]

Thanks everyone for your most insightful feedback.
I had to leave before the first reply came back and will review the replies today.

Yes, I am looking to find first 'empty data row' to answer that question,
and I often do know what the last 'data column' is. Ir rarely have sheets with
more than hundreds of data lines but may start working with ones that may have
50,000+ and that's where loop or sequential step codes could slow things down.

Again, I look forward to reviewing and testing the solutions present here in
this thread and learn a lot when some discussion takes place vs. one set answer...

TTom