VBA - deleting rows based on cell containing specific words + adding rows based on cell value

Question

What I am trying to do:

Exporting excel doc from Salesforce. I am trying to delete some rows based on a condition, then add rows based on a different set of conditions. Being new to VBA, I've browsed the forums and googled some sample codes, but cannot get them to work fully.

Adding rows:

Column C = Quantity. Need to add rows below based on the value in column C. If quantity = 3 - I want to add 2 blank rows below. If quantity = 4 - 3 blank rows below...

Tried:

Sub BlankLine()

Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

    Col = "C"
    StartRow = 15
    BlankRows = 1

        LastRow = Cells(Rows.Count, Col).End(xlUp).Row

        Application.ScreenUpdating = False

        With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) > 1 Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub

This works, but only adds a single row below - I need it to add rows based on the value of column C = 'quantity'.

Deleting Rows:

Column A = Product Names. If any product name contains MAINT or APP, I need to delete that row. Tried:

Sub test()

With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .AutoFilter 1, "*Maint*"
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

This also works, but deletes the top row once done, so I lose some information.

Any suggestions on how to approach this is greatly appreciated!

If for example C4 has a quantity of '4' - I'd like to add 3 empty rows below — bliq
– bliq, Commented Jun 8, 2016 at 20:13
Well the code would hopefully loop down to the next 'not empty' cell, so for example if C5 = 2 then I'd want to add 1 empty row below — bliq
– bliq, Commented Jun 8, 2016 at 20:19

user3598756 · Accepted Answer · 2016-06-08 22:02:50Z

0

As for inserting rows

For R = LastRow To StartRow + 1 Step -1
   With .Cells(R, Col)
      If .Value > 1 Then .Offset(1).Resize(.Value).EntireRow.Insert
   End With
Next R

While for filtering and deleting

Sub test()
With ActiveSheet
   .AutoFilterMode = False
   With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
      .AutoFilter Field:=1, Criteria1:=Array("*Maint*", "*APP*"), Operator:=xlFilterValues
      If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Offset(1).Resize(.Rows.Count -1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
   End With
   .AutoFilterMode = False
End With
End Sub

answered Jun 8, 2016 at 22:02

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3598756 Over a year ago

@bliq did you get through it?

Abe Gold · Accepted Answer · 2016-06-09 03:13:58Z

0

If .Cells(R, Col) > 1 Then
   numRows = val(.Cells(R, Col)) - 1
   For i = 1 to numRows
       .Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
   Next i
End If

edited Jun 9, 2016 at 3:13

answered Jun 8, 2016 at 19:35

Abe Gold

2,38718 silver badges31 bronze badges

2 Comments

bliq Over a year ago

Im getting an error for this - on the row "numRows = val(.Cells(R, Col) - 1". Could you maybe post the entire code you'd use - thanks!

Abe Gold Over a year ago

Oops. Left out a closing parentheses. See above.

Collectives™ on Stack Overflow

VBA - deleting rows based on cell containing specific words + adding rows based on cell value

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related