VBA .ClearContents for inserted Rows?

Question

So I have a code that finds three rows and insert the same rows below them, but I need to clear contents of these inserted rows, unfortunately, the selecetion applies for the first three rows and not those inserted.

    Sub Add_Timber()
'
' Add_Timber Macro
'

'
    Cells.Find(What:="Timber | Zakázky:", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireRow.Copy
    Selection.Offset(3, 0).Insert Shift:=xlDown


    Cells.Find(What:="Timber | JIRA ID:", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireRow.Copy
    Selection.Offset(3, 0).Insert Shift:=xlDown


    Cells.Find(What:="Timber | Hodiny:", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireRow.Copy
    Selection.Offset(3, 0).Insert Shift:=xlDown


End Sub

So in picture a red block is copied from the previous and I want to delete the data in the NEW INSERTED rows.

You should definitely read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Why not just insert blank rows? — SJR, Apr 15 '20 at 10:48
you might add some screenshots to better describe your data layout and your goal — HTH, Apr 15 '20 at 11:24
are those three rows always *consecutive* and in the *same order* (first "Zakázky", then "JIRA" and then "Hodiny")? — HTH, Apr 15 '20 at 12:18

score 0 · Answer 1 · answered Apr 16 '20 at 13:33

Since rows are Always conscutive and in the same ordwer, then you could make copy/insert in one shot

Sub Add_Timber()

    Dim f As Range
    Set f = Cells.Find(What:="Timber | Zakázky:", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)' try finding "Timber | Zakázky:"

    If Not f Is Nothing Then 'if succesfull
        With f.Resize(3).EntireRow ' reference found cell row along with its two consecutive ones
            .Offset(3).Insert ' insert referenced rows three rows below the referenced ones
            .Copy .Offset(3) ' copy referenced rows three rows below
        End With
    End If

End Sub

VBA .ClearContents for inserted Rows?

1 Answers1