Previous Topic Next Topic

Excel Insert Row after 4 Rows

Closed
Cthomp - 6 Jul 2010 à 12:10
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 6 Jul 2010 à 18:45
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


Related:

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
6 Jul 2010 à 18:45
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