Deleting Lines

  • Thread starter Thread starter open a adobe file from a command button
  • Start date Start date
O

open a adobe file from a command button

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 address removed)
"open a adobe file from a command button"
 
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"
 
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.
 
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"
 
Back
Top