Excel A macro to coninue a macro?

I have a spread sheet that is very large and am trying to make it smaller by combining every two rows. Some of the Columns in each row need to be averaged and some need to be min and max. I can make a macro that does it for the first two rows. How do I have the macro continue to the next two rows and do the samething, and continue till the end of the document?
Any help would be great.

You can use several kinds of loop to do this. The easiest one to get start with is a For-Next loop.

Try incorporating your code into this:

Public Sub SetConditions()

Dim i As Integer

For i = 0 To 100 Step 2
'+++++++++++++++++++++++++
' insert your code here
'+++++++++++++++++++++++++
Next

End Sub

Just change the number 100 to however many rows you've got.

Any problems just repost.

Use a For...To loop. For example.:

For x = 1 to 5000 Step 1 (replace 5000 with your last row of data)

Insert code here that you want repeated. Replace references to the first row with Rows(x) and references to the second row with Rows(x + 1). For example:

AverageC = (Cells(x,3).Value + Cells(x,4).Value) / 2
Cells(x.3).Value = AverageC
Rows(x + 1).Entirerow.Delete

Then put this code:

Next x

That will make it repeat the procedure with the next set of rows. If you wanted it to go two rows down before repeating, you could replace Step 1 with Step 2, but you shouldn't have to do that if/since your code deletes the second row each cycle of the loop, making row 3 row 2, row 4 row 3, etc.

Keep fiddling around with the code until it does what you want.

-Ben

I used the for statement and it works great for the Averages. I had to rewrite the code with the x variable so it could continue to the next rows. How do I write the Max() function with x variables? I tried doing ti and it keeps giving me not a sub or function error.
=max((x,5)(x+1,5))
Obvisouly this is wrong. But How do I make it right?
?
What do I do?

I take it you want to use the MAX worksheet function in VBA? Try something like this:

Maximum = Application.WorksheetFunction.Max(Range(Cells(x, 5), Cells(x + 1, 5)))
Cells(x, 5).Value = Maximum

-Ben O.

I wasn't using the application.worksheetfuntion command before the max function.
Thank you so much for your help.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

A macro to coninue a macro?

Re: A macro to coninue a macro?

Re: A macro to coninue a macro?

"For" Works Great. Now I can't get Max() to work

Re: "For" Works Great. Now I can't get Max() to work

Thanks SO Much!

Check out our Excel VBA Resources