Find the next 2 or 3 empty cells in a Column

[themick]

I am creating a results page for my macro. The results will vary day to day as data is processed.
I used the method below to find the next empty cell.

QUESTION: how do I modify this to find the next 2 empty cells and then place new data in the third?
Example: Column A1 has data to A10 thus A11 is the first empty cell. I want the macro then to place the next set of results starting in cell A13.
<code class="vb keyword">Sub</code> <code class="vb plain">Macro2()</code>
<code class="vb spaces"> </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb spaces"> </code><code class="vb keyword">Dim</code> <code class="vb plain">xCell </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb spaces"> </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">xCell </code><code class="vb keyword">In</code> <code class="vb plain">ActiveSheet.Columns(1).Cells</code>
<code class="vb spaces"> </code><code class="vb keyword">If</code> <code class="vb plain">Len(xCell) = 0 </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">xCell.</code><code class="vb keyword">Select</code>
<code class="vb spaces"> </code><code class="vb keyword">Exit</code> <code class="vb keyword">For</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces"> </code><code class="vb keyword">Next</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub


Thank you for any help provided on this.</code>

 

[alansidman]

Is there any data in any of the cells below A10. ie. Are you looking for the first empty cell in a column or the last cell in a column with data in it? Very important to understanding your issue.

 

[themick]

Is there any data in any of the cells below A10. ie. Are you looking for the first empty cell in a column or the last cell in a column with data in it? Very important to understanding your issue.

In my example, there would be no data below A10. However there could be as many as 4 possible sets of results.

So if after A10, the second set of data was applied starting at A13, (third empty cell past A10), and that had 5 units of data so data would now be in cells A13:A17.
So then the third set of data results would need to find the second set of 2 empty cells thus starting Data set 3 in A20 and so forth.

DATA RESULTS:
Data set 1: A1:A10
Data set 2: A13:A17
Data set 3: A20:A25
Data set 4: A28:>>

The range would change daily so the amount of data is not the focus, the next 2 empty cells that follow are so the macro results basically give the appearance of double or triple spacing between each set of data. Hope this helps, and thank you very much.

 

[Fluff]

Try

Range("A" & Rows.Count).End(xlUp).Offset(3).Select

 

[themick]

Try

Range("A" & Rows.Count).End(xlUp).Offset(3).Select

We worked on a different issue together before. Using the same WITH statement (as there sometimes may be no results from the four possible pages), I made the adjustment from Offset(1) to Offset(3) - works like a charm. I have a different post regarding same 'Results' page but not applicable to this so will follow-up here soon with that new post - thank you so much for your help on this!


Sheets("MONTHLIES").Select
With Sheets("MONTHLIES")
lr = Range("K" & Rows.Count).End(xlUp).Row
If lr > 1 Then
.Range("K2:K" & lr).SpecialCells(xlVisible).Copy
Sheets("RESULTS").Range("A" & Rows.Count).End(xlUp).Offset(3).PasteSpecial xlPasteValues
End If
End With

 

[Fluff]

Glad we could help & thanks for the feedback

 

[themick]

Followup -

Using the process discussed, how would I modify this routine so that only values greater than 0 would be copied?

.Range("K2:K" & lr).SpecialCells(xlVisible).Copy

 

[Fluff]

Put a filter on col K before you copy

 

[themick]

Put a filter on col K before you copy

Sometimes I make solutions harder than necessary. Forgot I could just put a plain simple filter and copy the results. Here is what I ended up with:

Sub X_COPYGREATERTHAN_0()
'
'
Sheets("MONTHLIES").Select
With Sheets("MONTHLIES")
lr = Range("K" & Rows.Count).End(xlUp).Row
'   SELECT, FILTER AND COPY COLUMN K 'NUMBERS>0'
    Columns("K:K").Select
    Selection.AutoFilter
    ActiveSheet.Range("K" & Rows.Count).AutoFilter Field:=1, Criteria1:=">0", _
        Operator:=xlAnd
    If lr > 1 Then
    Range("K2:K" & lr).SpecialCells(xlVisible).Copy
    Sheets("RESULTS").Range("A" & Rows.Count).End(xlUp).Offset(3).PasteSpecial xlPasteValues
    Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
    End If
    End With
'   CLEAR THE AUTOFILTER AND SELECTION
   Sheets("MONTHLIES").Select
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1").Select
'  END ON RESULTS SHEET, FIRST EMPTY CELL AFTER LAST OCCUPIED CELL
   Sheets("RESULTS").Select
   Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
     
End Sub

I am just learning how to understand VBA so in reviewing this - is there anything you would change to make it more compact/neater?.
Thank you again for all of your help.

 

[Fluff]

Maybe

Sub X_COPYGREATERTHAN_0()
'
'
With Sheets("MONTHLIES")
lr = .Range("K" & Rows.Count).End(xlUp).Row
'   SELECT, FILTER AND COPY COLUMN K 'NUMBERS>0'
    .Columns("K:K").AutoFilter
    .Range("K" & Rows.Count).AutoFilter Field:=1, Criteria1:=">0", _
        Operator:=xlAnd
    If lr > 1 Then
      .Range("K2:K" & lr).SpecialCells(xlVisible).Copy
      Sheets("RESULTS").Range("A" & Rows.Count).End(xlUp).Offset(3).PasteSpecial xlPasteValues
    End If
'   CLEAR THE AUTOFILTER AND SELECTION
    .Columns("K:K").AutoFilter
End With
    Application.CutCopyMode = False
'  END ON RESULTS SHEET, FIRST EMPTY CELL AFTER LAST OCCUPIED CELL
   Sheets("RESULTS").Select
   Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
     
End Sub