I have a macro question for excel...I am a novice when it comes to visual
basic functions because I don't program really at all. I run certain reports
every month that are in text format. For everyones wellbeing I import them
into excel and perform certain formatting functions. There is on report that
is quite big. What I have found is that every 35 rows there are 20 rows that
I delete in the report. I want a macro that will count 35 rows then delete
20 rows and then count another 35 rows and delete 20 until it reaches the end
of the selection. Any Ideas or suggestion would be appreciated. I am using
Microsoft Excel 2000 SP3.


Thanks Matthew Pietz

Matt

You could try something like this. Save your data before testing:

Sub DelRows()

Dim totRows As Long
Dim reps As Double
Dim counter As Integer

Range("A4").Select 'Assumes data starts in A4
totRows = Selection.CurrentRegion.Rows.Count
reps = Round(totRows / 55, 0)
For counter = 1 To reps
ActiveCell.Offset(35, 0).Select
ActiveCell.Offset(0, 0).Range("A1:A20").EntireRow.Delete
Next counter

End Sub


Regards
Rowan


"Matt Pietz" wrote:

I have a macro question for excel...I am a novice when it comes to visual
basic functions because I don't program really at all. I run certain reports
every month that are in text format. For everyones wellbeing I import them
into excel and perform certain formatting functions. There is on report that
is quite big. What I have found is that every 35 rows there are 20 rows that
I delete in the report. I want a macro that will count 35 rows then delete
20 rows and then count another 35 rows and delete 20 until it reaches the end
of the selection. Any Ideas or suggestion would be appreciated. I am using
Microsoft Excel 2000 SP3.


Thanks Matthew Pietz

Hello Matt,

Assuming Column A is a good column to test in terms of the last data cell,
open your immediate window in your VBE (Ctrl-G), and see if the following
procedure returns the ranges you'd like to delete:

Sub tester()
Dim y As Variant, z As Long
y = Evaluate("transpose(OFFSET(a1,(row(1:" & _
Range("a65536").End(xlUp).Row \ 35 & "))*35-1,0,21,1))")
For z = LBound(y) To UBound(y)
Debug.Print y(z).Address
Next
End Sub

If it does, you can use the array of range objects to delete your desired
ranges by iterating backwards through the array as such:

Sub delTime()
Dim y As Variant, z As Long
y = Evaluate("transpose(OFFSET(a1,(row(1:" & _
Range("a65536").End(xlUp).Row \ 35 & "))*35-1,0,20,1))")
Application.ScreenUpdating = False
For z = UBound(y) To LBound(y) Step -1
y(z).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub

Regards,
Nate Oliver