Macro to stop at the end of a worksheet

M

Mark

Hi

I'm writing a macro that I wish to go row by row through the worksheet until
it get to the end and then stops. It is a bit complex, so I won't try and
explain what I'm doing, but I've got about 30 workbooks with a few thousand
rows each.

Any suggestions on how to stop at the end if I use a similar way of moving
around as the following example (this example is moving by column, whereas
I'll want to work on each row then move down a row, moving by row.)

Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select

Many thanks for your help.
 
B

Bob Phillips

With ActiveCell
.Offset(0, 1).Resize(.End(xlDown).Row - .Row + 1).FormulaR1C1 =
"=RC[-1]*100"
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Mark

Many thanks Bob

I'll give it a try

Bob Phillips said:
With ActiveCell
.Offset(0, 1).Resize(.End(xlDown).Row - .Row + 1).FormulaR1C1 =
"=RC[-1]*100"
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Mark said:
Hi

I'm writing a macro that I wish to go row by row through the worksheet
until it get to the end and then stops. It is a bit complex, so I won't
try and explain what I'm doing, but I've got about 30 workbooks with a
few thousand rows each.

Any suggestions on how to stop at the end if I use a similar way of
moving around as the following example (this example is moving by column,
whereas I'll want to work on each row then move down a row, moving by
row.)

Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select

Many thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top