Auto Deletion of certain Rows in a spreadsheet

[Guest]

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

 

[Guest]

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

 

[William Benson]

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

 

[Guest]

When I run this, it doesn't seem to do anything?????
Any idea what I am doing wrong?
Thanks,
Danny

 

[Guest]

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

 

[Guest]

did you use the mouse cursor to highlight/select the column you want the
macro to run on. it only runs on the range you have selected. If you have
only one cell selected, it will do nothing.

other than that not positive what the problem is. i tested it before
posting and it ran fine.

unless your VBA settings require variable declaration (i didn't declare the
x in my code-but not sure i need to as the macro runs on my computer either
way). you could check by clicking tools/macro/visual basic editor (Alt-F11),
go to Tools/Options/Editor and ensure "Require Variable Declaration" is
unchecked.

 

[William Benson]

Hope you figured out that pasting the code in the forum message caused it to
shift down, sorry.

 

[William Benson]

I think he was referring to my code - I pasted his answer, way too late now
I am sure.