Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with Do While subroutine 1

Status
Not open for further replies.
Mar 6, 2003
157
JO
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
 
Hi,

When you do row/column deletes like this, it's a good idea to process from the bottom up...
Code:
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
 
Hi Skip,

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

Thanks,
Shawn
 
Hi

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

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

Enjoy,
Tony
 
Actually column &quot;A&quot; can have an &quot;x&quot;, &quot;xx&quot;, &quot;X&quot;, or &quot;XX.


Thanks,
Shawn
 
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 &quot;x&quot;, &quot;xx&quot;, &quot;X&quot;, or &quot;XX&quot; in the first column.

Thanks,
Shawn
 
Hi Skip,

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

Thanks,
Shawn
 
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, &quot;A&quot;).End(xlUp).Row To 1 Step -1
With Cells(r, &quot;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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top