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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Libra0099

Active Member
Joined
Apr 28, 2007
Messages
273
Thanks a lot,

I am new user for VB.

Please advice if I want to delete rows using "Column M"
 

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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
 

Libra0099

Active Member
Joined
Apr 28, 2007
Messages
273
Thanks a lot.

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


Can you please advice me how to learn VB?

I am average user for excel.
 

Libra0099

Active Member
Joined
Apr 28, 2007
Messages
273

ADVERTISEMENT

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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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