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.
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.