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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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:

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

miyaboy

New Member
Joined
Jun 3, 2010
Messages
23
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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
 

miyaboy

New Member
Joined
Jun 3, 2010
Messages
23
Sorry but the code does not work. Nothing happens after I run the macro.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Post the actual code that you have tried.
 

miyaboy

New Member
Joined
Jun 3, 2010
Messages
23
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
 

miyaboy

New Member
Joined
Jun 3, 2010
Messages
23
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")
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Then you need

If ucase(Range("A" & i).value) = "WOSE" Or ucase(Range("B" & i).value) = "WOSE" Then
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

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
Top