Help With a Loop That Deletes Rows

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

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






"Vecchia" wrote:

> 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
>
>

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
--
HTH...

Jim Thomlinson


"Vecchia" wrote:

> 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
>
>

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

"tjh" wrote:

> 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
>
>
>
>
>
>
> "Vecchia" wrote:
>
> > 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
> >
> >

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

"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Vecchia" wrote:
>
> > 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
> >
> >

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.
--
HTH...

Jim Thomlinson


"Vecchia" wrote:

> This worked great! I couldn't believe how fast it got rid of those rows!
> Thank you so much!
>
> "Jim Thomlinson" wrote:
>
> > 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
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Vecchia" wrote:
> >
> > > 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
> > >
> > >