Delete rows using Macro

Libra0099

Active Member
Joined
Apr 28, 2007
Messages
273
Hi,

I want to delete the rows if in "Column L" value is "Yes".

Please advice. Thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try

Code:
Sub DelL()
Dim LastRow As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlAutomatic
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For I = LastRow To 1 Step -1
    If Cells(I, 12).Value = "Yes" Then Rows(I).EntireRow.Delete
Next I
Application.ScreenUpdating = False
Application.Calculation = xlAutomatic
End Sub
 
Upvote 0
Hi VoGII

Nice code, but shouldn't the first Application.Calculation = xlManual?

Not trying to be picky, just trying not to confuse novice users - like myself.
 
Upvote 0
Hi VoGII

Nice code, but shouldn't the first Application.Calculation = xlManual?

Not trying to be picky, just trying not to confuse novice users - like myself.

Yes- well spotted. Corrected code

Code:
Sub DelL()
Dim LastRow As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For I = LastRow To 1 Step -1
    If Cells(I, 12).Value = "Yes" Then Rows(I).EntireRow.Delete
Next I
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

And to do the same for column M (=13)

Code:
Sub DelM()
Dim LastRow As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
For I = LastRow To 1 Step -1
    If Cells(I, 13).Value = "Yes" Then Rows(I).EntireRow.Delete
Next I
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
 
Upvote 0
Thanks a lot.

It working amazing............


Can you please advice me how to learn VB?

I am average user for excel.
 
Upvote 0
Hi VoG II

One more and I think Last question.


Now I want to delete rows, first using "Column L" data then delete rows using "column M" data.

Is this possible..............

Please advice. I am really thankful for you help.
 
Upvote 0
You can change out the 12 or 13 in the Cells() method with strings as well, such as "L" or "M"...

Code:
Cells(1, "M")
Cells(1, "L")

Also, want to learn VBA? Start reading the online forums, such as this one, VBAX, TOE, OzGrid, etc.

HTH
 
Upvote 0
If you mean that you want to delete rows where there is a "Yes" in column L or column M then try

Code:
Sub DelLM()
Dim LastRow As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For I = LastRow To 1 Step -1
    If Cells(I, 12).Value = "Yes" Or Cells(I, 13).Value = "Yes" Then Rows(I).EntireRow.Delete
Next I
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

Where to learn VBA? A good place to start is by reading the posts on this board. You can also look at the Help within VBA. In the Visual Basic Editor if you click in a keyword (example: For) and press F1 this will display the Help on that topic.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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