Deleting Rows with Macros

[Guest]

Starting at a specific cell, if the cell contents are less than or equal to zero ( or blank), then delete that row and the next three rows. After the deletion, if the now current cell's contents are still less than or equal to zero (or blank) then repeat, else if the now current cell's contents are greater than zero, then skip down four rows and repeat the process with that cell
We used to be able to do this with LOTUS, but the macro no longer works in the latest version of EXCEL - the one difference was that LOTUS started from the bottom of the file and worked upwards - it's OK if EXCEL needs to start from the bottom of the file as well. I didn't write the original LOTUS macro and I'm a novice at macros.

 

[Anya]

I'm not exactly sure what you're trying to achieve, as in
Excel spreadsheet you will always have the same number of
rows (65536) and columns, regardless of how many
rows/columns you delete. However, if you're simply trying
to get rid of blank rows between the rows with data, you
can use the following macro (please use it on a back-up
copy first, as I'm not sure if this is what you're looking
for):

Sub ClearRows()

Dim Counter As Integer

Counter = 0
ThisWorkbook.ActiveSheet.Range("A1").Select
Do Until Counter = 65536
If ActiveCell.Value < 0 Or IsEmpty(ActiveCell) =
True Or ActiveCell.Value = "" Then
ActiveCell.Rows("1:4").EntireRow.Delete
shift:=xlUp
Else
ActiveCell.Offset(1, 0).Activate
End If
Counter = Counter + 1
If ActiveCell.End(xlDown).Row = 65536 Then
Exit Do
End If
Loop

End Sub

 

[Dave Peterson]

One more way:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim delRng As Range
Dim dummyRng As Range
Dim NumberOfRowsToDelete As Long

Set wks = ActiveSheet
With wks

NumberOfRowsToDelete = 4

FirstRow = Application.InputBox(prompt:="Start at what Row?", Type:=1)

If FirstRow = 0 Then
Exit Sub
End If

Set dummyRng = .UsedRange 'try to reset usedrange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row



.Cells.Interior.ColorIndex = xlNone

For iRow = FirstRow To LastRow Step NumberOfRowsToDelete
If .Cells(iRow, "A").Value <= 0 Then
If delRng Is Nothing Then
Set delRng = .Cells(iRow, "A") _
.Resize(NumberOfRowsToDelete, 1)
Else
Set delRng = Union(delRng, .Cells(iRow, "A") _
.Resize(NumberOfRowsToDelete, 1))
End If
End If
Next iRow

If delRng Is Nothing Then
'nothing to do
Else
'delRng.EntireRow.Delete
delRng.Interior.ColorIndex = 6
End If

End With

End Sub

change that last bit to really do the delete. I just colored the rows that
should be deleted--nice for testing first???

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm