When I try to run this I get a compile error in the line that starts with
"For i ="
Any idea what it is or if I am doing something wrong??
Thanks,
Danny

"William Benson" wrote:

A bit shorter, maybe not as generic

Sub Del_OneThroughFour()
Dim i As Long, Del_Count As Long
Const SkipRows = 4

For i = (SkipRows + 1) * Int(ActiveSheet.UsedRange.Count / (SkipRows + 1))
To (SkipRows + 1) Step -(SkipRows + 1)
ActiveSheet.Range("A" & i - 1, "A" & i - SkipRows).EntireRow.Delete
Del_Count = Del_Count + 1
Next i
'we might have left up to SkipRows extras not yet deleted - remove these now
ActiveSheet.Range("A" & Del_Count + 1, "A" & Del_Count + 5).EntireRow.Delete
End Sub



"JMB" wrote in message
...
There was an earlier post using SumProduct to return every Nth item in a
list
(that was pretty cool), but I couldn't find it. Programmatically - you
could
use the following and copy it into a VBA module.

Select the column you want evaluated, run macro, and this should go
through
and delete all but every 5th item.

Of course, back up your data in case this does not end up doing what you
need.

Sub DeleteData()
Const N = 5
Dim SourceRange As Range
Dim RangeToDelete As Range

On Error Resume Next

Set SourceRange = Selection

If SourceRange.Rows.Count < 2 Or _
SourceRange.Columns.Count 1 Then Exit Sub

For Each X In SourceRange
If (X.Row - SourceRange.Cells(1, 1).Row) Mod N < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = X
Else: Set RangeToDelete = Union(RangeToDelete, X)
End If
End If
Next X

RangeToDelete.EntireRow.Delete

End Sub


"scratching my head" wrote:

I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie:
Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in
the
macro to tell it how many sets of 4 consecutive rows I need deleted (with
one
good row between each bad set of 4). The rows that need to be deleted are
not
entirely blank, some of the cells have data (not needed) but there are a
few
rows that are entirely blank. It needs to delete the rows irreguardless
of
any data in that row. Help is appreciated as I am not a programmer but
you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have
no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data
and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny