PC Review


Reply
Thread Tools Rate Thread

Deleting Lines

 
 
open a adobe file from a command button
Guest
Posts: n/a
 
      9th Apr 2010
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
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      9th Apr 2010
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


 
Reply With Quote
 
JLGWhiz
Guest
Posts: n/a
 
      9th Apr 2010
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



 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      9th Apr 2010
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

>
>

 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      9th Apr 2010
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

>>
>>

 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      9th Apr 2010
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


 
Reply With Quote
 
JLGWhiz
Guest
Posts: n/a
 
      9th Apr 2010
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
>>>
>>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Lines GoodLevitaion Microsoft Word Document Management 1 9th Jun 2008 04:26 PM
Deleting unused lines between used lines? =?Utf-8?B?U3RldmVs?= Microsoft Excel Setup 1 25th Nov 2005 12:58 AM
Deleting blank lines between data lines Phenom Microsoft Excel Discussion 3 21st Nov 2003 06:36 PM
Deleting lines really deletes lines! EmmanuelleZ Microsoft Excel Programming 2 2nd Oct 2003 03:32 PM
Deleting lines really deletes lines! EmmanuelleZ Microsoft Excel Misc 2 29th Sep 2003 06:19 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:14 AM.