Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DELETING LINES THAT ARE THE SAME IN A TABLE | Excel Discussion (Misc queries) | |||
Deleting unused lines between used lines? | Setting up and Configuration of Excel | |||
Deleting multiple lines | Excel Discussion (Misc queries) | |||
Deleting lines in a macro | Excel Programming | |||
Deleting lines really deletes lines! | Excel Programming |