Excel Insert Row after 4 Rows

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
For some reason I am having the hardest time figuring out this simple macro I need to insert a row after 4 rows for a large amount of data. I have seen rizvisa's reply https://ccm.net/forum/affich-340646-how-to-move-data-in-rows-to-columns but can't get it to work on my excel. Any help would be appreciated, Clay


1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
That will not do for you. In that the objective was to move row into columns. You want to add a row after every fourth row

Sub AddBlankRow()
Dim Cell As Range
Dim sTgtSht As String
Dim lMaxRows As Long
Dim lRow As Long

    sTgtSht = "Sheet1"
    
    Set Cell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        
    If Cell Is Nothing Then GoTo End_Sub
    lMaxRows = Cell.Row
    
    Set Cell = Nothing
    
        For lRow = 5 To lMaxRows Step 5
        Sheets(sTgtSht).Rows(lRow).Insert
        lMaxRows = lMaxRows + 1
    
    Next lRow
    
End_Sub:

End Sub