Help With Macro!

[Raconteur]

Hi,

I'd be indebted to anyone who can help me with some Macro problems i'm having.

I have a Macro, which works perfectly, for deleting any row where a cell in the active column does not contain a value:

Sub del_1()
Do While Not IsEmpty(ActiveCell)
If Not ActiveCell.Value Like "*MrExcel*" THEN
ActiveCell.EntireRow.Delete shift = xlDown
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

What I am now trying to do, is modify it, so that i can run a macro which deletes all rows where the cell in the active column does not contain a number of different values, not just the single value.

For example, i'd like to delete all rows without 'MrExcel', or 'Macro', or 'Forum' in the cells in the active column.

Any help would be massively appreciated.

Many thanks,

D.

 

[MickG]

Try This. Select any cell in specific column.Run code.

Dim Last, Dn As Integer, oLt, oRt, Col As String
Col = Split(Selection.Address, "$")(1)

Last = Range(Col & Rows.Count).End(xlUp).Row
For Dn = Last To 1 Step -1
Select Case Cells(Dn, Col)
Case Is = "Mr Excell"
Case Is = "Macro"
Case Is = "Forum"
Case Else
Rows(Dn).EntireRow.Delete shift:=xlDown
End Select
Next Dn

Regards Mick

 

[Raconteur]

Mick, that worked a treat, thank you ever so much.

Just to note, i had to break up my data into 4 parts, as it was 65k rows, and the Macro wouldn't allow me to run it on anything over about 20k.

Many many thanks.

 

[MickG]

Hi, I dont' know the answer to that.
Do you actually want to delete the rows, or just put all the "Select case" items in a list one below the other.??
With 65K rows it's going to take some while !
Mick

 

[Raconteur]

Delete the rows i don't need, to leave behind the ones i do.

It only took me about 5 mins to chop it up, and run your Macro, that's a lot quicker than any other method i know.