Need help with Do While subroutine 1

[shawnmandel]

I need to create the following subroutine with a loop:

Look at the cell in column A, if it has an 'x', go to cell in next row in column A. If it encounters any other value delete the entire row.

Thanks,
Shawn

 

[SkipVought]

Hi,

When you do row/column deletes like this, it's a good idea to process from the bottom up...

for r = cells(cells.rows.count, "A").end(xlup).row to 1 step -1
  with cells(r,"A")
    if .value <> &quot;x&quot; then .entirerow.delete
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[shawnmandel]

Hi Skip,

You suggestion did not seem to function as expected. The result was all rows were deleted.

Thanks,
Shawn

 

[SkipVought]

Did you have

x

or

X

in that column?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi

No need for a loop at all. This is another one you can do with a filter ..

With Columns("A:A")
    .AutoFilter Field:=1, Criteria1:="=*x*"
    .EntireRow.Delete Shift:=xlUp
End With

Enjoy,
Tony

 

[shawnmandel]

Actually column "A" can have an "x", "xx", "X", or "XX.


Thanks,
Shawn

 

[shawnmandel]

Hi Tony,

Since I have a somewhay complex template, the filter does not give me the result that I wanted since I need to handle any "x", "xx", "X", or "XX" in the first column.

Thanks,
Shawn

 

[shawnmandel]

Hi Skip,

I created a simple spreadsheet with random "x"'s in column A and it appears that all rows are being deleted including the ones with the "x"'s.

Thanks,
Shawn

 

[shawnmandel]

Hi Skip,

Just to let you know that your suggestion worked very well. I modified the code to look like the following:

For r = Cells(Cells.Rows.Count, "A&quot.End(xlUp).Row To 1 Step -1
With Cells(r, "A&quot
If (.Value <> &quot;x&quot; And .Value <> &quot;xx&quot; _
And .Value <> &quot;X&quot; And .Value <> &quot;XX&quot Then _
.EntireRow.Delete
End With
Next

 

[SkipVought]

Here's another option...

  With Cells(r, "A")
    Select Case UCase(.Value)
      Case "X", "XX"
        .EntireRow.Delete
    End Select
  End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com