Delete Everything Except.........Help pls

[omegadeltaphi]

I am trying to delete everything in one column except for the rows that contain the word "Active" all other cells need to simply remain blank.




Thanks in Advance

Steve

 

[Rick Rothstein]

Since you didn't tell us, I'll assume that column is Column A. Again, because you didn't tell us, I am also going to assume that this column does not contain any formulas. Given these assumptions, here is a macro for you to try...

Sub KeepActives()
  On Error Resume Next
  With Columns("F")
    .Replace "Active", "=Active", xlWhole
    .SpecialCells(xlCellTypeConstants).ClearContents
    .SpecialCells(xlCellTypeFormulas).Value = "Active"
  End With
End Sub

 

[omegadeltaphi]

B column.

I only have two columns and many rows.

this did not work????

 

[Rick Rothstein]

B column.

I only have two columns and many rows.

this did not work????

Please answer all the questions so we will know how to proceed...

Columns A and B?

Can the "Active" word be in either column or just one (which one if only one)?

Did you want both cells in the row cleared or only the one with the word "Active" in it?

If both cells, did you want the cells below it to move up or just leave the blank row in place?

Are there any formulas in the cells?

 

[omegadeltaphi]

Can the "Active" word be in either column or just one (which one if only one)? It is in column B

Did you want both cells in the row cleared or only the one with the word "Active" in it? I need all the cells in column B cleared except for "active"

If both cells, did you want the cells below it to move up or just leave the blank row in place? Blank

Are there any formulas in the cells? No

Column A Column B

info &%!*
info *%@#
info active
------------------------------------------------

i need to get rid of all the bad words.

sorry for not making myself clear.

Thx Rick


Steve<!-- / message -->

 

[Rick Rothstein]

Okay, give this macro as try...

Sub KeepActives()
  On Error Resume Next
  With Columns("B")
    .Replace "Active", "=Active", xlWhole
    .SpecialCells(xlCellTypeConstants).ClearContents
    .SpecialCells(xlCellTypeFormulas).Value = "Active"
  End With
End Sub

 

[omegadeltaphi]

kewl

thx