Macro Writing Help?

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Can anyone help me with writing a macro that will delete
any rows that contain an "X" in a cell of a particular
column? The "X" would be indicator that the row should be
deleted by the macro.

Thank you.
 
Richard,

Try something like

Dim RowNdx As Long
Dim EndRow As Long
Dim StartRow As Long
Dim ColNdx As Long
EndRow = 100 ' << CHANGE
StartRow = 10 ' << CHANGE
ColNdx = 3 '<< CHANGE
For RowNdx = EndRow To StartRow Step -1
If Cells(RowNdx,ColNdx).Value = "X" Then
Rows(RowNdx).Delete
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Two versions:

The first one knows the column that you are expecting the X's to be in (in
the sample its column 2 or 'B'). The second doesn't. Both are case sensitive
by design.


Sub DeleteTheXRows1()

Dim rw As Range
Dim col As Range

Set col = Columns(2)

For Each rw In Intersect(col, ActiveSheet.UsedRange)
If rw = "X" Then
rw.EntireRow.Delete
End If

Next

End Sub



This version will delete ANY row that contains a ANY cell with a single,
capital X. It does not assume the X's are in any particular column.


Sub DeleteTheXRows2()

Dim rw As Range
Dim rngFound As Range

On Error Resume Next

For Each rw In ActiveSheet.UsedRange.Rows

Set rngFound = rw.Find(What:="X", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)

If Not rngFound Is Nothing Then
rw.Delete
Set rngFound = Nothing
End If

Next

End Sub
 
Back
Top