vba to insert 3 blank rows at the change in col h

[fvisions]

Hello, I have read the posts in this forum looking for one similar to what I am looking for but I am unable to change them to what I am looking for. I have a workbook with multiple worksheets. I am have created command buttons on one tab to step the end user through to do multiple things. The last piece is to press the command button on the command button tab and have the sheet labeled "Installation wkg" and insert 3 blank rows at the change in column H (sheet has col A:AD) I am not sure where to being so I attempted to copy other examples and edit but that has not worked for me. Is it possible to help me get started?

 

[JLGWhiz]

What do you mean by

the sheet labeled "Installation wkg" and insert 3 blank rows at the change in column H

?

Do you want this to happen at the instant when a change is made in column H? If so, then Worksheet_Change event would be the easiest way to do that.
Code would go into the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Target.Offset(1).Resize(3).EntireRow.Insert
End If
Application.EnableEvents = True
End Sub

 

[Joe4]

See of this does what you want:

Sub InsertBlankRows()

    Dim lrow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column H with data
    lrow = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all rows backwards in column H
    For r = lrow To 2 Step -1
'       Check to see if value in column H of current row is different from row above
        If Cells(r, "H") <> Cells(r - 1, "H") Then
'           Insert three blank rows
            Rows(r & ":" & r + 2).Insert
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[MARK858]

Just another version

Sub insertrows3()
    Dim insRrw As Long
    Application.ScreenUpdating = False

    With Sheets("Installation wkg")
        For insRrw = .Cells(.Cells.Rows.Count, "H").End(xlUp).Row To 2 Step -1
            If .Cells(insRrw, "H") <> .Cells(insRrw - 1, "H") Then .Rows(insRrw).Resize(3).Insert
        Next insRrw
    End With
End Sub

 

[fvisions]

What do you mean by

the sheet labeled "Installation wkg" and insert 3 blank rows at the change in column H

The workbook as multiple worksheets and I wanted the command button on the worksheet labeled "Command buttons" to know that I wanted the task (insert rows) on the worksheet labeled "Installation wkg"
Maybe I am over thinking the process?

copied your code and received a run-time error 424 Object Required

If Target.Cells.Count > 1 Then Exit Sub
[CODE=vba]If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    Target.Offset(1).Resize(3).EntireRow.Insert
End If
Application.EnableEvents = True
End Sub

 

[MARK858]

copied your code and received a run-time error 424 Object Required

You have managed to copy the first part of a code tag [CODE=vba] into the 2nd line of the code that isn't in JLGWhiz's code, but it isn't for running from a button though.

 

[fvisions]

I am new to copying code, is there a trick to coping from here? I highlighted, control c

 

[MARK858]

is there a trick to coping from here?

Click the

icon in the top right of the code window and paste in your VBE window

 

[JLGWhiz]

The workbook as multiple worksheets and I wanted the command button on the worksheet labeled "Command buttons" to know that I wanted the task (insert rows) on the worksheet labeled "Installation wkg"
Maybe I am over thinking the process?

My question was directed at the 'Change' in column H. Are you referring to any change made by a user in column H, or are you referring to where one series of values ends and another series of values begins? @MARK848 and @Joe4 addressed the value series, and my code addresses the user input changes.

 

[fvisions]

I used marks code and copied it correctly (thank you for that lesson) the code adds 3 rows at row 2 as well as at the change of column H. This is my fault , I failed to tell you have a header row in row 1