Looping a macro

[Toadums]

Hello, i was wondering how i would loop this macro (and others like it) all the way down to cell 66500 or what ever the last one is.

Here is my macro

Sub Macro1()
'
' Macro1 Macro
'

'
    Rows(n + 5).Select
    Selection.ClearContents
Loop 
End Sub

Thanks.

[rylo]

Hi

For i = 1 to cells(rows.count,1).end(xlup).row

next i

will give you a range of row to cycle through, assuming that column A will contain an entry that determines the last row.

The bit of you code

Rows(n + 5).Select

shows you want to clear every 5th row, but what is the value of n? Do you want to clear rows 5,10,15....?

If you have a row that can give a mod of 0, then you could do something like

if i mod 5 = 0 then
  cells(i,1).entirerow.clearcontents
end if

HTH

rylo

[Toadums]

hmm...you said mod a couple times, what do you mean by it?

your macro got an error on the row

cells(i,1).entirerow.clearcontents

and also, what exactly does this mean:

For i = 1 to cells(rows.count,1).end(xlup).row

next i

I just ask because i want to be able to learn how to do loops my own

Thanks.

Paul

[rylo]

Paul

1) Mod it the function that

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

So if you divide 10 by 5, then the remainder is 0. Basically it is a way to determine every 5th row.

2) What you are doing is creating a loop that counts from 1 to ???. The ??? is determined to be the last filled row in column A (

cells(rows.count,1).end(xlup).row

) For every iteration, i increments by 1. This is the default, and you can change the amount it increments by using the step argument. Have a look at the help file on the for loop.

3) I put some numbers into a range on a sheet from A1:A50. Then run the code

Sub aaa()
  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If i Mod 5 = 0 Then
      Cells(i, 1).EntireRow.ClearContents
    End If
  Next i
End Sub

It will clear out every 5th row (ie 5,10,15....)

HTH

rylo

[Toadums]

Awesome, Thanks! it works now and it pointed me int he right direction for looping stuff