Near the end of your Sub, the following lines:
'------------------------
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select

'------------------------
becomes:
'------------------------
Selection.AutoFill Destination:=Range("B5",Cells(Rows.count,"B").end( xlup))
'------------------------

(you can delete the "Range("B5:B22135").Select" line

HTH
--
AP
"GMB_Excel" a écrit
dans le message de
...

OK, thanks. But how do I use this if I don't write my macro in visual
basic? I only know how to record a macro and then edit it (to a certain
extent). So, this is what my macro looks like (part of it) when I copy a
formula to the whole column by using the autofill function:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:B3"),
Type:=xlFillDefault
Range("A3:B3").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "1"
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]-R[-1]C[-1]=0,R[-1]C,R[-1]C+1)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select


It is the "if" statement that I want to copy to the end of my data. How
would I modify this macro to do it?

Thanks a lot for your help.

George




JE McGimpsey Wrote:
One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


In article ,
GMB_Excel
wrote:

OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a
range
that goes from your starting cell to the "end" (last nonblank cell
in
range)?

George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile:
http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979