excelvbarow

Insert Multiple Rows and Values in Excel in various Places with VBA


I have a list of items in a sheet. I want to put two rows between them and add the same two tasks (Test, Install) to each item. The code below adds all the lines to the top of the sheet and not to individual rows. (The first half clears out rows I don't need). (Ross is just a place holder). I found this code and am trying to make it do what I want. I am not very good at this.

I've watched too many videos and search through code all day and cant figure this out. Any help would be appreciated.

Function Column B
Fun1
Fun2
Function Task
Fun1
Test
Install
Fun2
Test
Install
Sub DeleteRowsBasedonCellValue()
    
    'Declare Variables
    Dim LastRow As Long, FirstRow As Long
    Dim Row As Long

    With ActiveSheet
       'Define First and Last Rows
       FirstRow = 1
       LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
       LastRow = ActiveSheet.UsedRange.Rows.Count
       'MsgBox LastRow

       'Loop Through Rows (Bottom to Top)

       For Row = LastRow To FirstRow Step -1
           If .Range("C" & Row).Value = "0" Then
              .Range("C" & Row).EntireRow.Delete
           End If
       Next Row

    End With

    With ActiveSheet
        FirstRow = 1
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For Row = LastRow To FirstRow Step -1
            If .Range("C" & Row).Value <> "" Then
                Rows("3:7").Insert Shift:=xlDown
                'Range("D" & FirstRow).Value = "Ross"
                FirstRow = FirstRow + 1
            End If
        Next Row
    End With

End Sub

Ive tried videos and searching other code. I've modified a bunch of different things I've found but none do what i need it to do.


Solution

  • Building on the comment I left under:

            Dim rowNum as Long:  For rowNum = LastRow To FirstRow Step -1
                If .Range("C" & rowNum).Value <> "" Then
                    .Range(.Rows(rowNum+1),.Rows(rowNum+2)).Insert Shift:=xlDown
                    .Range("D" & rowNum+1).Value = "Test"
                    .Range("D" & rowNum+2).Value = "Install"
                End If
            Next rowNum
    

    Notice the follow-through in dot notation; you had several instances that was not occurring.

    Additionally, the had the reverse loop, but weren't using your looping element to make entries (rowNum after I changed it, since Row is a property and you don't want to have confusion).


    Edit

    Updated Next Row to Next rowNum.

    Something to keep in mind, as your table in the post does not clearly show the columns: if Columns(3)/Columns("C") is blank, then you will have no entries to your work.