GoTo Cell and loop down until blank cell found, insert new row as above

[LittlegmoonBH]

Hi All,

I'm trying to add a macro to a template that' proving tricky.

Basically, the macro needs to go to cell "C4" then loop down a variable amount of times until it hits a blank cell, it then needs to add a blank row using the formatting from the above populated row without any data.

 

[Joe4]

Welcome to the Board!

See if this does what you want:

Sub MyMacro()

    Dim r As Long
    
'   Find first blank row below C4
    If Range("C5") = "" Then
        r = 5
    Else
        r = Range("C4").End(xlDown).Row + 1
    End If
    
'   Insert blank row
    Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub

 

[LittlegmoonBH]

Legend! It gives me what I need, the only thing I need to figure out is how bring down the formatting from the cells above. The above adds a row, but doesn't bring down the borders

 

[Joe4]

Hmmm...

Mine seemed to extend it down automatically. Let try it explicitly like this:

Sub MyMacro()

    Dim r As Long
    
'   Find first blank row below C4
    If Range("C5") = "" Then
        r = 5
    Else
        r = Range("C4").End(xlDown).Row + 1
    End If
    
'   Insert blank row
    Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
'   Copy formatting from row above
    Rows(r - 1).Copy
    Range("A" & r).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub