CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

insert a blank row above a certain string in excel vba

I have a workbook containing multiple sheets and each sheet has different products information. In Column B of each sheet, last row is the total I would like to insert a blank row if it found "TOTALS" in column B and I would like to insert it right above TOTALS row.

Right now codes run through without any issue but nothing happened neither. I don't necessarily need to call the insert row if you can consolidate them into one procedure.

Sub Main()
  Dim ws As Worksheet

  For Each ws In ActiveWorkbook.Worksheets

  Call insert_row


  Next ws

End Sub


Sub insert_row()
Dim r As Long
  For r = 100 To 1 Step -1
    If Cells(r, 2).Value = "TOTALS" Then Rows(r).Insert
  Next r
End Sub

Upvotes: 0

Views: 206

Answers (1)

Sam
Sam

Reputation: 5721

You need to use ws as a parameter to your sub. Something like this:

Sub Main()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        insert_row ws
    Next ws
End Sub

Sub insert_row(ws As Worksheet)
    Dim r As Long
    For r = 100 To 1 Step -1
        If ws.Cells(r, 2).Value = "TOTALS" Then ws.Rows(r).Insert
    Next r
End Sub

Upvotes: 1

Related Questions