VBA Code: Delete Entire Row based on Condition

jbeck

New Member
Joined
Jun 7, 2010
Messages
20
Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.

Maybe:

Code:
Sub jbeck()
Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

    If Range("A" & i) = 100 Then
        Range("A" & i).EntireRow.Delete Shift:=xlUp
    End If
    
Next i

Application.ScreenUpdating = True
   
End Sub
 
Last edited:
Upvote 0
Here's the VBA for an autofilter
Code:
Sub FilterDelete()
Dim rspn As Variant
LR = Cells(Rows.Count, "A").End(xlUp).Row
rspn = InputBox("What do you want to delete?")
Range("$A2:$A" & LR).AutoFilter Field:=1, Criteria1:=rspn
Range("$A$2:$A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub
Mucjh more efficient than a loop!!

lenze
 
Upvote 0
If I have 2 criteria on 2 separate columns -A & B, how do I modify the macro to work?

As long as 1 of the criteria is met, the row should be deleted.


Thanks
 
Upvote 0
If I have 2 criteria on 2 separate columns -A & B, how do I modify the macro to work?

As long as 1 of the criteria is met, the row should be deleted.


Thanks


Try something like this

Code:
Sub jbeck()
Dim i As Long
Dim LR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i) = 100 Or Range("B" & i) = "Completed" Then
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Sub jbeck()
Dim i As Long
Dim LR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = LR To 2 Step -1
If Range("A" & i) = "WOSE" Or Range("B" & i) = "WOSE" Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Okay for the first two columns A & B as long as "WOSE" appears in a row, I want the row to be deleted.

The "WOSE" is being generated from the excel formula IF(c2="ddd","WoSE","NONE")
 
Upvote 0
Then you need

If ucase(Range("A" & i).value) = "WOSE" Or ucase(Range("B" & i).value) = "WOSE" Then
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top