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

Try using data>filter>autofilter>>>

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"open a adobe file from a command button"
<(E-Mail Removed)> wrote in
message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
>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

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"
<(E-Mail Removed)> wrote in
message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
> <(E-Mail Removed)> wrote in
> message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
>>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
>
>

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

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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"
>> <(E-Mail Removed)> wrote in
>> message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
>>>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
>>
>>

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"
<(E-Mail Removed)> wrote in
message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
> 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

The NG gods will get you for that. <g>


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry... I didn't mean to post my macro against your message.
>
> --
> Rick (MVP - Excel)
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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"
>>> <(E-Mail Removed)> wrote in
>>> message news:147DA2F8-D642-461C-B0EC-(E-Mail Removed)...
>>>>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
>>>
>>>