loop to delete rows...
 

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

loop to delete rows...
 

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

loop to delete rows...
 

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

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

loop to delete rows...
 

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

loop to delete rows...
 

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