Help With a Loop That Deletes Rows

[Guest]

I have an Excel sheet that has 3 columns. Column 1 is the same throughout -
HQCCA1. What I would like is to start at row 1 and look at Column 3 and if
the data in row 1 is XS, then delete the row. Otherwise, go on to the next
row and look to see if it is XS, etc through to the end of the sheet. Here
is what I wrote and nothing happens (I must admit I tried using a sample to
do this.):

Sub LoopRange1()
x = ActiveCell.Row
y = x + 1
Do while Cells(x,1).Value = HQCCA1
If Celss (x,3).Value = "XS" Then
Cells(x,1).EntireRow.Delete
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
End Sub

 

[Guest]

This is one way.
You almost had it. I made a couple of revisions.

Option Explicit
Sub LoopRange1()
Dim x As Long

x = 1
'y = x + 1
Do While Cells(x, 1).Value = "HQCCA1"

If Cells(x, 3).Value = "XS" Then
Cells(x, 1).EntireRow.Delete
Else
x = x + 1
End If
'y = x + 1

Loop
End Sub

 

[Guest]

So if I understand correctly you want to find all instances of XS in Column C
and delete the row? You can give this code a try. It uses find to create a
range of all of the XS cells and then delete those rows. It is more efficinet
than search all of the cells one at a time.

Sub DeleteRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range
Dim rngFirst As Range
Dim rngToDelete As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("C")
Set rngFound = rngToSearch.Find(What:="XS", LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "Sorry. Nothing to Delete"
Else
Set rngFirst = rngFound
Set rngToDelete = rngFound
Do
Set rngToDelete = Union(rngToDelete, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngToDelete.EntireRow.Delete
End If

End Sub

 

[Guest]

It didn't work. I have header rows throughout the sheet should I delete them?

 

[Guest]

This worked great! I couldn't believe how fast it got rid of those rows!
Thank you so much!

 

[Guest]

It is fast for two reasons...
1. It uses find instead so looking at each cell individually.
2. It just does one big delete at the end. Deletes are time consuming.
Glad to help.