Deleting Unique Rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MWS - 22 Mar 2006 13:30 GMT

Hello, I have the following code to delete blank rows within a worksheet.

'Deletes blank lines, for advance filterring
[a1:a50000].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Question: Is there a method to delete certain rows, if a particular cell
within a row meets certain criteria? For instance, I'd like to reference
range a1:a50000, and delete each row within this range, if "Sub Total" is the
value in any cell within column A.

Is this possible and if so, how?

Any and All Help Is Appreciated - Thank You

Reply to this Message

Nicholas B - 22 Mar 2006 14:38 GMT

Try this

Sub EraseSubTotalRows()
Rows2Erase = ""
For MyRow = 1 To 50000
TgtCell = "A" + Trim(Str(MyRow))
If UCase(Trim(CStr(Range(TgtCell).Value))) = UCase("Sub Total") Then
MyRowTxt = Trim(Str(MyRow))
Rows2Erase = Rows2Erase + "," + MyRowTxt + ":" + MyRowTxt
End If
Next MyRow
Rows2Erase = Mid(Rows2Erase, 2) ' Gets Rid of first comma
Range(Rows2Erase).Select
Selection.Delete Shift:=xlUp
End Sub

> Hello, I have the following code to delete blank rows within a worksheet.
>
[quoted text clipped - 9 lines]
>
> Any and All Help Is Appreciated - Thank You

Reply to this Message