Delete cells that don't meet certain criteria

[vrobinson0304]

Hello,

I'm trying to write a macro that will delete all rows that don't have certain values in column A. The values are determined in cells O1, O2, and O3. I got the code below to work for values in O1 and O2, however it does not allow me to set a third criteria for O3. I believe I may have to do a an array. Can someone please help! Thank you.

[COLOR=#4f6228]Sub DelRows()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/COLOR]
[COLOR=#4f6228]    Application.ScreenUpdating = False<o:p></o:p>[/COLOR]
[COLOR=#4f6228]    ActiveSheet.AutoFilterMode = False<o:p></o:p>[/COLOR]
[COLOR=#4f6228]    On Error Resume Next<o:p></o:p>[/COLOR]
[COLOR=#4f6228]        With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)<o:p></o:p>[/COLOR]
[COLOR=#4f6228]        .AutoFilter Field:=1, Criteria1:="<>" & Range("O1").Value, Criteria2:="<>" & Range("O2").Value<o:p></o:p>[/COLOR]
[COLOR=#4f6228]                .Offset(4).SpecialCells(xlCellTypeVisible).EntireRow.Delete<o:p></o:p>[/COLOR]
[COLOR=#4f6228]        .AutoFilter<o:p></o:p>[/COLOR]
[COLOR=#4f6228]    End With<o:p></o:p>[/COLOR]
[COLOR=#4f6228]    Application.ScreenUpdating = True<o:p></o:p>[/COLOR]
[COLOR=#4f6228]End Sub[/COLOR]

 

[pboltonchina]

Try this on a copy of your data

If (Range("A" & i).Value <> Range("O1").Value Or Range("O2").Value Or Range("O3").Value Then Rows(i).Delete

 

[Peter_SSs]

1. What version of Excel are you using?

2. What columns does your main data occupy?

3. How many rows of data are there (approximately)?

4. Could there be more than 3 values in column O?

 

[Rick Rothstein]

While the approach is different than the one you posted, I believe this macro will do what you want...

Sub DeleteAllExceptForO1toO3()
  Dim UnusedColumn As Long, LastRow As Long
  Const StartRow As Long = 4
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=IF(COUNTIF(R1C[-1]:R3C[-1],RC[-15]),""X"","""")"
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With
  Columns(UnusedColumn).Clear
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Rick

If column O is definitely the last column used then UnusedColumn could be hard-coded (like StartRow) to 16. If column O is not the last column used then your COUNTIF formula will fail since it assumes column O is 1 column and column A is 15 columns to the left of the formula column. Suggest a change to

.Formula = "=IF(COUNTIF(O$1:O$3,A" & .Row & "),""X"","""")"

 

[Rick Rothstein]

Rick

If column O is definitely the last column used then UnusedColumn could be hard-coded (like StartRow) to 16. If column O is not the last column used then your COUNTIF formula will fail since it assumes column O is 1 column and column A is 15 columns to the left of the formula column. Suggest a change to

.Formula = "=IF(COUNTIF(O$1:O$3,A" & .Row & "),""X"","""")"

****, I'm annoyed! Not at you for commenting... thank you for catching that by the way... rather, at me for forgetting about my mental note to myself to go back and splice in (UnusedColumn-1) for the hard-coded column number in my test setup. However, your comment got me to thinking and I have now decided not to assume Column O (the word list column, which now can be changed via the WordListColumn constant) is the last filled data column. Here is a more generalized routine which allows there to be data after the word list and for the word list to be set longer than 3 words. I did not set up a test to make sure that StartRow was larger than WordListCount, I'm just assuming the OP will not have them overlap. The StartRow can be set a row number larger than one plus the WordListCount in case headers are ever required in the future.

Sub DeleteAllExceptForO1toO3()
  Dim UnusedColumn As Long, LastRow As Long, ListColumn As Long, ListCount As Long
  Const StartRow As Long = 4
  Const WordListColumn As String = "J"
  Const WordListCount As Long = 3
  ListColumn = Columns(WordListColumn).Column
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=IF(COUNTIF(R1C" & ListColumn & ":R" & WordListCount & "C" & ListColumn & ",RC[-" & (UnusedColumn - 1) & "]),""X"","""")"
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With
  Columns(UnusedColumn).Clear
  Application.ScreenUpdating = True
End Sub

This is probably more flexibility than the OP will ever need, but there is no speed penalty for including it, so I figured "what the hell". Again, thanks for catching my oversight.

 

[vrobinson0304]

Rick, Thank you for your help. Unfortunately that deleted all the data. Any thoughts on why? Or how to fix that?

Peter, to answer your questions:

1. What version of Excel are you using? 2007

2. What columns does your main data occupy? Columns A-N

3. How many rows of data are there (approximately)? ~2,000

4. Could there be more than 3 values in column O? No there will only be three values in column O - in O1, O2, and O3.

 

[Rick Rothstein]

Rick, Thank you for your help. Unfortunately that deleted all the data. Any thoughts on why? Or how to fix that?

Before I posted the code, I did a test to make sure you could move the word list column from Column O to another column. My test was for Column J which it looks like I accidentally left in the code I posted. Change this line of code...

Const WordListColumn As String = "J"

to this...

Const WordListColumn As String = "O"

and I think it will work for you. Sorry for the confusion... it was just before 5am local time and I was going to bed for the night (I sleep funny hours) and I guess I was kind of tired.

 

[vrobinson0304]

amazing. thank you so much!

 

[vrobinson0304]

Another question related to this. Is there any way to then sort by column A based on the order of the values in O1, O2, and O3. So for example if O1 = Packaging, O2 = Electric, and O3 = Airlines, the data would look like:

Packaging B C D E F G H I J K L M N
Packaging B C D E F G H I J K L M N
Packaging B C D E F G H I J K L M N
Electric B C D E F G H I J K L M N
Electric B C D E F G H I J K L M N
Electric B C D E F G H I J K L M N
Airlines B C D E F G H I J K L M N
Airlines B C D E F G H I J K L M N
Airlines B C D E F G H I J K L M N