Clear contents if x is not in the row

  • Thread starter Thread starter Mighvik
  • Start date Start date
M

Mighvik

I need to clear the contents in all rows except rows with
the word "ALL" in it.

Can someone help?
Thanks
 
Sub Tester9()
Dim rng As Range
Dim fAddr As String
Columns(1).Insert
Set rng = Cells.Find("All", AFter:=Range("IV65535"), _
LookIn:=xlValues, LookAt:=xlPart)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
Cells(rng.Row, 1).Value = "X"
Set rng = Cells.FindNext(rng)
Loop While rng.Address <> fAddr
Else
ActiveSheet.UsedRange.Rows.Delete
Columns(1).Delete
Exit Sub
End If
Set rng = Columns(1).SpecialCells(xlBlanks)
rng.EntireRow.Delete
Columns(1).Delete
End Sub
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clear contents of last two rows of data on sheet 6
Clearing contents 4
Clear Contents of Specific Cells in Last Row 2
Clear contents of cells hidden by autofilter 3
clear contents of cell 3
Clear contents on change 5
Clear all rows except first two 7
Clearing the contents of a row 2

Back
Top