Prev: Conditional Format Formula to color row based on text
Next: weird and spontaneous combobox resizing
From: open a adobe file from a command button on 9 Apr 2010 15:12
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
From: Don Guillett on 9 Apr 2010 16:26
Try using data>filter>autofilter>>>

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"open a adobe file from a command button"
<openaadobefilefromacommandbutton(a)discussions.microsoft.com> wrote in
message news:147DA2F8-D642-461C-B0EC-F1C91EA7DCF3(a)microsoft.com...
>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

From: JLGWhiz on 9 Apr 2010 16:41
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"
<openaadobefilefromacommandbutton(a)discussions.microsoft.com> wrote in
message news:147DA2F8-D642-461C-B0EC-F1C91EA7DCF3(a)microsoft.com...
>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


From: Rick Rothstein on 9 Apr 2010 16:50
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" <JLGWhiz(a)cfl.rr.com> wrote in message
news:u7ejQUC2KHA.3568(a)TK2MSFTNGP04.phx.gbl...
> 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"
> <openaadobefilefromacommandbutton(a)discussions.microsoft.com> wrote in
> message news:147DA2F8-D642-461C-B0EC-F1C91EA7DCF3(a)microsoft.com...
>>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
>
>
From: Rick Rothstein on 9 Apr 2010 17:03
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"
<openaadobefilefromacommandbutton(a)discussions.microsoft.com> wrote in
message news:147DA2F8-D642-461C-B0EC-F1C91EA7DCF3(a)microsoft.com...
> 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

 |  Next  |  Last
Pages: 1 2
Prev: Conditional Format Formula to color row based on text
Next: weird and spontaneous combobox resizing