Hi Prakash

i would do it like this:
(It's possible, that there exist other/better solutions)
'----------------------------------------------------------------------------------
Sub test()

Dim lastcell As Integer
For i = 2 To 10000
If ActiveSheet.Range("A" & i) = "" Then
lastcell = i - 1
Exit For
End If
Next i

Range("B2").Select
Selection.AutoFill Destination:=ActiveSheet.Range("B2:B" & lastcell),
Type:=xlFillDefault
Range("B2:B" & lastcell).Select
End Sub
'----------------------------------------------------------------------------------

not the fastest code, but it should work

hth

Cheers Carlo


"Prakash" wrote:

Hi experts,

Need your help/inputs

1. I have an excel file with a columnA of values, for example say upto 27
rows.
2. Based on these column values, I'm having one more column and computing
values into columnB.
3. I'm using
Selection.AutoFill Destination:=Range("B2:B27"), Type:=xlFillDefault
Range("B2:B27").Select
4. Since this macor needs to be used for other excel files as well, I do not
wish to hardcode the range value 27 in the macro.

How can I dynamically change this value of the range? so that if the values
are less or ore than 27, the macro should work.

Thank you in advance.

With Regards