deleting rows automatically using a maco or vba

  • Thread starter Thread starter outrigger
  • Start date Start date
O

outrigger

I have a report that comes to me in Excel 2003. I need to remove the first 3
and last 3 rows. The report in between may vary on the number of rows
however. It is basically a repetion I want to forgo.
 
This assumes that col A is filled on the last row:

Sub RowRemover()
Dim n As Long
Dim s As String
n = Cells(Rows.Count, "A").End(xlUp).Row
s = n - 2 & ":" & n
Rows(s).Delete
Rows("1:3").Delete
End Sub
 
Sub removetopandbottomthreerows()
dim lr as long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Rows(lr - 2).Resize(3).Delete
Rows(1).Resize(3).Delete
End Sub


Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
 
I use this to remove blank rows.

Hydra

' This function removes all blank rows from the working area of the
Spreadsheet.
Sub EraseBlankRows()
Dim rng As Range, nrows As Long, ncols As Long

With ActiveSheet
Set rng = .Cells.SpecialCells(xlLastCell)
nrows = rng.Row
ncols = rng.Column
Nrow = 1
For Nrow = nrows To 1 Step -1
Set rng = .Cells(Nrow, 1).EntireRow
If BlankCellCount(rng) = ncols Then rng.Delete xlShiftUp
Next Nrow
End With
End Sub


Private Function BlankCellCount(ByVal rng As Range) As Long
On Error Resume Next
BlankCellCount = rng.SpecialCells(xlCellTypeBlanks).Count
End Function
 
Back
Top