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

Hi
the problem for this kind of operation is that you have to work from
the bottom to the top (not vice versa). e.g. try
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow To 1 Step -1
If instr(cells(row_index,1).value, "Total")0 " then
Rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


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

Dim lrow as Long, i as Long
lrow = Cells(rows.count,1).End(xlUp).row

for i = lrow to 1 step -1
If instr(1,cells(i,1).Text,"total",vbTextCompare) Then
cells(i,1).EntireRow.Delete
End If
Next i

--
Regards,
Tom Ogilvy


"Froglegz" wrote in message
...
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

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

The trick when deleting rows is to index backwards (Step - 1), so the
following code, should help you on your way

Sub deleteTotalSubTotal()
Dim lbottom As Long
Dim x As Long
lbottom = Range("A65536").End(xlUp).Row
For x = lbottom To 1 Step -1
If InStr(1, Range("A" & x).Value, "SubTotal") Or _
InStr(1, Range("A" & x).Value, "Total") Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Froglegz" wrote in message
...
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

One option:

Sub delRows()

Dim i As Long
Dim LR As Long

LR = Cells(1).End(xlDown).Row

For i = LR To 2 Step -1
If InStr(1, Cells(i, 1), "Total", vbTextCompare) 0 Then
If Left(Cells(i, 1), 5) = "Total" Then
If Not Left(Cells(i - 1, 1), 8) = "SubTotal" Then
Rows(i).Delete
End If
Else
Rows(i).Delete
End If
End If
Next

End Sub


RBS


"Froglegz" wrote in message
...
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