Need help to auto delete 4 of every 5 rows

[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

 

[theDude]

Hi Danny,

Here are two versions that will do what you need...

This version prompts the user to enter the number of sets to delete:

Code:
--------------------
Sub delete4Rows1()
Dim userCount As String, setCount, x, y As Integer
' Code point to jump back to if user makes data entry error...
Start:
' Prompt user to enter number of sets to delete...
userCount = InputBox("Enter number of sets to delete: ")
' Validate user entry to ensure it's a number (if not, alert user & start over)...
If Not IsNumeric(userCount) Then
' Alert user of data entry error...
MsgBox "ERROR: you must enter a number."
GoTo Start
End If
' If user didn't cancel the data entry dialog, start delete process...
If userCount <> "" Then
' Convert user entry to integer for loop routine limiter...
setCount = CInt(userCount)
' Select first row to delete...
ActiveSheet.Range("A2").Select
' Run set delete routine based on user entry...
For x = 1 To setCount
' Run row delete routine 4 times...
For y = 1 To 4
ActiveCell.EntireRow.Delete
Next y
' Skip over row not to delete...
ActiveCell.Offset(1, 0).Select
' Loop again based on user-entered set count...
Next x
End If
End Sub
--------------------

This version doesn't prompt the user; it determines the number based on
the range of data in the spreadsheet:

Code:
--------------------
Sub delete4Rows2()
Dim x As Integer
' Start at first cell to evaluate...
ActiveSheet.Range("A1").Select
' Loop through every cell in used range...
For x = 1 To ActiveSheet.UsedRange.Rows.Count
' If empty or space in cell, delete row...
If ActiveCell.Value = "" Or ActiveCell.Value = " " Then
ActiveCell.EntireRow.Delete
Else
' Otherwise, a value exists in cell so skip row...
ActiveCell.Offset(1, 0).Select
End If
' Move to next cell in sheet and repeat...
Next x
End Sub