Dynamic range
 

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

Dynamic range
 

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

Dynamic range
 

You can prompt the user to supply the range:

Dim r As Range
Set r = Range(InputBox("Where"))
Selection.AutoFill Destination:=r, Type:=xlFillDefault
--
Gary's Student


"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