Prev: Conditional Format Formula to color row based on text
Next: weird and spontaneous combobox resizing
From: Rick Rothstein on 9 Apr 2010 17:02
Sorry... I didn't mean to post my macro against your message.

--
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
news:eX0PnZC2KHA.4332(a)TK2MSFTNGP02.phx.gbl...
> 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: JLGWhiz on 9 Apr 2010 18:29
The NG gods will get you for that. <g>


"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
news:OCxNMgC2KHA.5316(a)TK2MSFTNGP05.phx.gbl...
> Sorry... I didn't mean to post my macro against your message.
>
> --
> Rick (MVP - Excel)
>
>
> "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
> news:eX0PnZC2KHA.4332(a)TK2MSFTNGP02.phx.gbl...
>> 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
>>>
>>>


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