Insert rows in Column using VBA

Dear Experts:

this macro inserts 5 rows whenever the macro hits a blank cell in Column A. I am pretty sure that there is a better way to write this macro, i.e. the line 'Cells(Row, 1).EntireRow.Insert' shouldn't be repeated 5 times.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Public Sub InsertRowOnBlank()
    Dim Max As Integer
    Dim Row As Integer

    Max = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For Row = Max To 2 Step -1  ' Start at the bottom, go up
    If Cells(Row, 1).Value = "" Then
            Cells(Row, 1).EntireRow.Insert
            Cells(Row, 1).EntireRow.Insert
            Cells(Row, 1).EntireRow.Insert
            Cells(Row, 1).EntireRow.Insert
            Cells(Row, 1).EntireRow.Insert
        End If
    Next Row
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You can simply do this...

Public Sub InsertRowOnBlank()
    Dim Max As Integer
    Dim Row As Integer

    Max = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For Row = Max To 2 Step -1  ' Start at the bottom, go up
    If Cells(Row, 1).Value = "" Then
            Rows(Row & ":" & Row + 3).Insert
                 End If
    Next Row
End Sub

Open in new window


Saurabh....
0
 
dsackerContract ERP Admin/ConsultantCommented:
Take out your individual Insert lines. Replace it with:

Range(Cells(Row, 1), Cells(Row + 4, 1)).EntireRow.Insert
Public Sub InsertRowOnBlank()
    Dim Max As Integer
    Dim Row As Integer

    Max = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For Row = Max To 2 Step -1  ' Start at the bottom, go up
        If Cells(Row, 1).Value = "" Then
            Range(Cells(Row, 1), Cells(Row + 4, 1)).EntireRow.Insert
        End If
    Next Row
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Great job from both of you, both codes work just fine. Thank you very much for your professional support.

Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.