Hi,

In principal not difficult but how is it decided what the size of the fill
range is. For example if it were to the same row as the adjacent column G

lastrow = Cells(Rows.Count, "G").End(xlUp).Row
would find the last used row so the fill range could be

Selection.AutoFill Destination:=Range("H2:H" & Lastrow)

Mike
"lwm" wrote:

Hi all

I am trying to make a small modification to this recorded macro. The macro
works fine as is. The problem is that we want to replace
Selection.AutoFill Destination:=Range("H2:H2000")

with the ability to dynamically change the range. In other words sometimes
it will be 100 rows and sometime it will be 15000 rows or 50 rows.

X= 25

i.e.
Selection.AutoFill Destination:=Range(RC!: RC +X)

Thanks for the help


Sub CREDITS_NEGATIVE()
' CREDITS_NEGATIVE Macro

' Macro recorded 8/16/2007
'
Dim x As Integer
x = 25

'
Selection.AutoFilter

Columns("H:H").Select

Selection.Insert Shift:=xlToRight

Range("H2").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]=""c"",-RC[-1],RC[-1])"

Range("H2").Select

Selection.AutoFill Destination:=Range("H2:H2000")

Range("H2:H2000").Select

Calculate

Selection.Copy

Range("G2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Columns("H:H").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Range("H10").Select

End Sub