A little more Macro Magic required?

[mikemcbain]

G'day Macro Magicians

I would like to add some code to a macro that I use daily.

I would like it to select Column C which predominantly contains two digit numbers however some Rows contain the Text "No."

I would like the macro code to remove all those Rows containing No. EXCEPT in Row 1.

Waiting with great anticipation.

Old MIke.

 

[Michael M]

Hey Mike
How about

Sub MM1()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .Replace "No", "#N/A", xlWhole, , False, , False, False
    Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[mikemcbain]

Hey Mike
How about

Sub MM1()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .Replace "No", "#N/A", xlWhole, , False, , False, False
    Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

Michael - you must be ageing?
Worked like a charm after I put the little dot in "No."
Thank you once again.
Old Mike.

 

[Michael M]

Aahh yes....sorry I thought the period was not included....glad it worked for you..

 

[Peter_SSs]

FWIW, assuming those numbers are in fact numbers and not text values, there is no need to do the replacement suggested to allow use of the SpecialCells feature.
(I have also assumed the "No." values are the only text values in the column, are not the result of formulas & that at least one "No." exists below row 1.)

If the above is correct then you could also try this one-liner

Sub RemoveRows()
  Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).EntireRow.Delete
End Sub

 

[mikemcbain]

Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).EntireRow.Delete

Thank you Peter

Yes your one liner works beautifully.

Old Mike.

 

[Rick Rothstein]

Since SpecialCells only operates on the UsedRange, you could also use this macro (same assumptions as Peter posted)...

Sub RemoveRows()
  Range("C2:C" & Rows.Count).SpecialCells(xlConstants, xlTextValues).EntireRow.Delete
End Sub

 

[Peter_SSs]

You're welcome.