Deleting Lines
 

I have a worksheet with about 11,000 lines. Between lines there is a "Total"
line and a "Blank" line. How can remove these two lines using some code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

Try using datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

See if this will do it:

Sub tract()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If Application.CountA(sh.Rows(i)) = 0 Then
Rows(i).Delete
ElseIf Application.CountA(sh.Rows(i)) = 1 And _
Application.CountIf(sh.Rows(i), "Total") = 1 Then
Rows(i).Delete
End If
Next
End Sub




"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

If you are looking for a macro, then give this one a try...

Sub RemoveTotalAndBlankLines()
Dim U As Range, C As Range, FirstAddress As String
With ActiveSheet.Columns("A")
Set C = .Find("Total", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If U Is Nothing Then
Set U = C.EntireRow.Resize(2)
Else
Set U = Union(U, C.EntireRow.Resize(2))
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
U.Delete
End If
End With
End Sub

Note that I have assumed you will run this from the worksheet with your
"Total" rows on them and that your "Total" text is in Column A, hence the
object used in the With statement (change this statement to suit your actual
needs); and also note that I have assumed the row under the "Total" row is
always "blank" and, so, I don't bother checking it.

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message
...
See if this will do it:

Sub tract()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If Application.CountA(sh.Rows(i)) = 0 Then
Rows(i).Delete
ElseIf Application.CountA(sh.Rows(i)) = 1 And _
Application.CountIf(sh.Rows(i), "Total") = 1 Then
Rows(i).Delete
End If
Next
End Sub




"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

Sorry... I didn't mean to post my macro against your message.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If you are looking for a macro, then give this one a try...

Sub RemoveTotalAndBlankLines()
Dim U As Range, C As Range, FirstAddress As String
With ActiveSheet.Columns("A")
Set C = .Find("Total", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If U Is Nothing Then
Set U = C.EntireRow.Resize(2)
Else
Set U = Union(U, C.EntireRow.Resize(2))
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
U.Delete
End If
End With
End Sub

Note that I have assumed you will run this from the worksheet with your
"Total" rows on them and that your "Total" text is in Column A, hence the
object used in the With statement (change this statement to suit your
actual needs); and also note that I have assumed the row under the "Total"
row is always "blank" and, so, I don't bother checking it.

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message
...
See if this will do it:

Sub tract()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If Application.CountA(sh.Rows(i)) = 0 Then
Rows(i).Delete
ElseIf Application.CountA(sh.Rows(i)) = 1 And _
Application.CountIf(sh.Rows(i), "Total") = 1 Then
Rows(i).Delete
End If
Next
End Sub




"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some
code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

If you are looking for a macro, then give this one a try...

Sub RemoveTotalAndBlankLines()
Dim U As Range, C As Range, FirstAddress As String
With ActiveSheet.Columns("A")
Set C = .Find("Total", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If U Is Nothing Then
Set U = C.EntireRow.Resize(2)
Else
Set U = Union(U, C.EntireRow.Resize(2))
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
U.Delete
End If
End With
End Sub

Note that I have assumed you will run this from the worksheet with your
"Total" rows on them and that your "Total" text is in Column A, hence the
object used in the With statement (change this statement to suit your actual
needs); and also note that I have assumed the row under the "Total" row is
always "blank" and, so, I don't bother checking it.

--
Rick (MVP - Excel)



"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some code,
verses doing it by hand?

Thank You In Advance
William

Deleting Lines
 

The NG gods will get you for that. <g


"Rick Rothstein" wrote in message
...
Sorry... I didn't mean to post my macro against your message.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If you are looking for a macro, then give this one a try...

Sub RemoveTotalAndBlankLines()
Dim U As Range, C As Range, FirstAddress As String
With ActiveSheet.Columns("A")
Set C = .Find("Total", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If U Is Nothing Then
Set U = C.EntireRow.Resize(2)
Else
Set U = Union(U, C.EntireRow.Resize(2))
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
U.Delete
End If
End With
End Sub

Note that I have assumed you will run this from the worksheet with your
"Total" rows on them and that your "Total" text is in Column A, hence the
object used in the With statement (change this statement to suit your
actual needs); and also note that I have assumed the row under the
"Total" row is always "blank" and, so, I don't bother checking it.

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message
...
See if this will do it:

Sub tract()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If Application.CountA(sh.Rows(i)) = 0 Then
Rows(i).Delete
ElseIf Application.CountA(sh.Rows(i)) = 1 And _
Application.CountIf(sh.Rows(i), "Total") = 1 Then
Rows(i).Delete
End If
Next
End Sub




"open a adobe file from a command button"
osoft.com wrote in
message ...
I have a worksheet with about 11,000 lines. Between lines there is a
"Total"
line and a "Blank" line. How can remove these two lines using some
code,
verses doing it by hand?

Thank You In Advance
William