VBA Row Break

[Littlemalky]

I have an array of data that I have sorted relative to column "S" from "Smallest to Largest" in my code. This is a weekly report and the amount of data will change weekly; however, the only things fixed are the columns, while the amount of rows will vary as the data changes.

I need to make a break in the rows relative to column "S" between the numbers 3 and 4 that are displayed in the cells. Starting from S2 there will be a bunch of "1's" going down to an unknown amount, then "2's" below that, etc. All i need is to insert 4 blank rows in between the end of the "3's" and the beginning of the "4's" in column "S".

How do I do this exactly?

 

[AlphaFrog]

Sub Insert_Rows()

    Dim Found4 As Range
    
    Set Found4 = Range("S:S").Find(4, [S1], , xlWhole, xlByRows, xlNext)
    
    If Not Found4 Is Nothing Then
        Found4.Resize(4).EntireRow.Insert
    Else
        MsgBox "Can't find ""4"""
    End If
End Sub

 

[Littlemalky]

Thank you, AlphaFrog. That looks logical; however, it just keeps telling me "Can't Find "4"" when I run it, when in fact there are 4's.

 

[AlphaFrog]

It looks for the numeric value 4 only and not 44 or 4.1 or 4 with a space after it.

Can you confirm the 4's in your data are just numeric fours e.g.
=S10 = 4
True or False?

 

[Littlemalky]

Oh, ok. In that case, there is actually a formula in this column, "NETWORKDAYS". Is that what is messing it up?

 

[AlphaFrog]

Maybe. What's the formula.

Also, do the =S10=4 test True or False? (Where S10 is a cell with a four)

 

[AlphaFrog]

Maybe try this...

Set Found4 = Range("S:S").Find(4, [S1], [COLOR="Red"]xlValues[/COLOR], xlWhole, xlByRows, xlNext)

 

[Littlemalky]

That worked!!!! Thank you so much! I deeply appreciate it.

 

[Littlemalky]

Hey AlphaFrog, you were helpful for me before. I have another question that relates to this same piece of code. I've realized that I may have not stated my question generally enough, I used two assumptions that may not necessarily exist. One being that there will be 3's in this column and the other being that there will be 4's in this column.

This data changes every week when I run the report, and I'm trying to anticipate error. There may be a case where there are just 1's and 2's in the column, or any array of numbers, and 3's and 4's may not exist at all. The numbers represent days. I need there to always be a break in rows at the point between 3 and 4. So say I sort the column and it goes:

S2 = 0
S3 = 1
S4 = 2
S5 = 5

I still need the break in between the end of the 2's and the beginning of the 5's even if there are no 3's or 4's in the data. Do you see what I'm saying?

 

[AlphaFrog]

Sub Insert_Rows()

    Dim Found As Range
    Dim counter As Long, sMax As Long
    
    sMax = Application.Max(Range("S:S"))
    counter = 4
    
    Do
    
        Set Found = Range("S:S").Find(counter, Range("S1"), xlValues, xlWhole, xlByRows, xlNext)
        If Not Found Is Nothing Then Found.Resize(4).EntireRow.Insert
        counter = counter + 1
        
    Loop Until Not Found Is Nothing Or counter > sMax
    
    If Found Is Nothing Then MsgBox "Can't find four or larger."
    
End Sub