loop to delete rows...
The Total row is skipped because deleting a row renumbers the rows, but
not the loop variable.
You can reduce it to one loop by looping "backwards" (from highest row
to lowest row), or do something like this:
Public Sub DelRows()
Dim rCell As Range
Dim rDelete As Range
For Each rCell in Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Text like "Total*" Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then _
rDelete.EntireRow.Delete
End Sub
In article ,
"Froglegz" wrote:
here's the issue: I'm trying to delete all the rows which
start with "total:" and "subtotal:" in column A. I had to
use 2 loops because when there is a subtotal row directly
followed by a total row, the total row is "skipped". I'm
not sure that I'm making myself very clear but any help
would be appreciated... Ideally I'd like to replace the 2
loops by 1.
Here's what I have so far:
sub delRows()
Dim c As Range
For Each c In Range(Range("a1"), Range("a1").End
(xlDown)).Cells
If c.Text Like "SubTotal*" Then
c.EntireRow.Delete
End If
Next c
For Each c In Range(Range("a1"), Range("a1").End
(xlDown)).Cells
If c.Text Like "Total*" Then
c.EntireRow.Delete
End If
Next c
end sub
|