Using VBA to Delete Rows Automatically

pips30

New Member
Joined
Aug 13, 2007
Messages
40
Can anyone save my sanity and offer assistance with this problem:

What I need VBA to do is scan the data in a certain column (in this case column I entitled "Type") and if it picks up a certain letter (either a "C" "D" or "K") in a range starting from row 2 then Delete the whole row and condense them so I am only left with the data that corresponds to letters "E" and "R"...

Does there exist a macro that can accomplish this for me; the alternative doesn't even bare thinking about as the range can be anything up to row 6k

I have tried looking on the Net and have seen example of similiar macros, alas I'm completely new to this and cannot adapt it to my needs.

Any help will be met with multiple "hallelujahs" :biggrin:

Thanks in advance
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
pips30

Welcome to the MrExcel board!

See if this is what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    lr = Range("I" & Rows.Count).End(xlUp).Row
    <SPAN style="color:#00007F">With</SPAN> Range("I1:I" & lr)
        .AutoFilter Field:=1, Criteria1:="=C", _
            Operator:=xlOr, Criteria2:="=D"
        .Offset(1).EntireRow.Delete
        .AutoFilter Field:=1, Criteria1:="=K"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
HALLELUJAH!!!

Peter - thanks for the welcome and the swift response, but most all thank you for the solution.
It is great; it does exactly what I need it to do!

So thanks again for sharing your knowledge; really appreciate it...
 
Upvote 0
HALLELUJAH!!!

Peter - thanks for the welcome and the swift response, but most all thank you for the solution.
It is great; it does exactly what I need it to do!

So thanks again for sharing your knowledge; really appreciate it...
No problem, BUT you did promise...
Any help will be met with multiple "hallelujahs"

:LOL:
 
Upvote 0
oh go on then, you can have another one: "HALLELUJAH"

seriously though, this has helped so much; I never would have come up with what you suggested. Although I am playing around with macros more now so hopefully they'll become more acquainted with the old grey matter...

I love what Excel can do; and Array forumlas rule!

:wink:
 
Upvote 0
Deleting Rows Based on Certain Criteria

Can anyone help with this:

In one of the columns I am working with there are a number of codes, e.g: BT28BA001 - DN13HX001 - HLDDN1S05 (this would be in column E)

What I need VBA to do is pick out the codes that contain the letters D and N and then delete the entire row... :eek:

I'm sure it's possible I just can't figure out how to do it :oops:

Any help would be really appreciated
 
Upvote 0
Re: Deleting Rows Based on Certain Criteria

Can anyone help with this:

In one of the columns I am working with there are a number of codes, e.g: BT28BA001 - DN13HX001 - HLDDN1S05 (this would be in column E)

What I need VBA to do is pick out the codes that contain the letters D and N and then delete the entire row... :eek:

I'm sure it's possible I just can't figure out how to do it :oops:

Any help would be really appreciated
Do the D and N have to be in the same row?

If so:
Do they have to be together (eg DN13HX001) or could they be apart (eg ADGN6X43)?
Do they have to be in that order or could the N also come before the D sometimes?
 
Upvote 0
Re: Deleting Rows Based on Certain Criteria

Do the D and N have to be in the same row?

If so:
Do they have to be together (eg DN13HX001) or could they be apart (eg ADGN6X43)?
Do they have to be in that order or could the N also come before the D sometimes?

Yes.
The DN will always be together buried somwhere within the 9 digit code. It can also appear anywhere within the string e.g at the beginning, middle or end.

These codes are references to Sites across the world; what I'm trying to do is isolate the codes that denote that site is a District Node (DN), thus leaving me with everything else, i.e Customer Sites...

It's a good point about the N coming before the D as this could happen, but only in terms of it being part of the code and not referencing it as a District Node (123DN6789); VB would have to ignore D's and N's in any other permutation.

The logical argument would be
123DN6789 or DN3456789 = Delete
12N4567D9 or D2345N789 = Do not Delete

I hope this clarifies my predicament...

Thanks in advance
 
Upvote 0
So try an AutoFilter like in the previous code I suggested, but use a line like this:
Code:
.AutoFilter Field:=1, Criteria1:="=*DN*"
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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