Deleting Rows which don't meet specified conditions in VBA

snideysimon

New Member
Joined
Sep 6, 2006
Messages
10
Hi
I am wanting to write a macro which will delete all rows which do not meet a specified condition - the long hand version would be as follows:

If Range("J3") <> "Jan" Then Rows("3:3").Delete
If Range("J4") <> "Jan" Then Rows("4:4").Delete

Obviously I don't want to repeat this out 65000 times, is there a quicker way to do this
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

PeregrinTook

Board Regular
Joined
Feb 9, 2006
Messages
157
Simon,

Assuming you have headers in row 1:

Code:
Sub MyDelete()
Dim i As Long

For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
    If Cells(i, "J") <> "Jan" Then Rows(i).Delete
Next i

End Sub

But if you want to include row 1 in the loop change to this in line 4:

Cells(Rows.Count, "J").End(xlUp).Row To 1

HTH
PeregrinTook
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, snideysimon
Welcome to the Board !!!!!

If all data are next to each other without empties you can use
autofilter
custom: "is not equal" to "Jan"
select all rows
delete
show all data again

when using this in a macro you get
Code:
Sub deleterows_autofilter()
'header in J1
'data without gaps

Range("J1").AutoFilter Field:=1, Criteria1:="<>Jan" ', Operator:=xlAnd
Range("J2:J" & Range("J1").End(xlDown).Row).EntireRow.Delete
ActiveSheet.ShowAllData
End Sub
since there is no loop, this is very quick even with large ranges

kind regards,
Erik
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
its not my post but we have find the solution form your code

thanks
 
Last edited:

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
its not my post but we have find the solution form your code

thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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