Unknown Range in Macro

[scone57]

Having trouble changing a fixed range to a variable range in VBA.
When creating the macro, I used a fixed range, but now need to change this
to a variable, for future spreadsheets.

Range("R4").Select
Selection.AutoFill Destination:=Range("R4:R886")
Range("R4:R886").Select

Can anyone help?

 

[Don Guillett]

Tell us more. Do you want to set the range or just the last row of this
range.

 

[scone57]

I am not sure of the range as it will change monthly, so cant set it,but at
the moment the column states FALSE, when there is no data in the previous
columns.

 

[Luke M]

What controls your variable? (what logic?) Assuming you already have that
part figured out, and you want to fill to row xCount (xCount being your
variable) you could change your script to

Range("R4").Select
Selection.AutoFill Destination:=Range("R4:R" & xCount)
Range("R4:R" & xCount).Select

Other ideas for your variable, depending on the logic you want, would be to
count cells and set this value equal to xCount, or have xCount equal the
value of some cell in the spreadsheet. (ie, xCount = Range("a1").value )

 

[Stefi]

First you should tell us how do you want to determine the range limits. E.G
if you can determine the last used row as the end of the range then

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Selection.AutoFill Destination:=Range("R4:R" & lastrow)


Regards,
Stefi

„scone57†ezt írta:

 

[FSt1]

hi
key would be to find the last row in your range and assign it to a variable.
maybe....
Dim lr As Long
lr = Cells(Rows.Count, "R").End(xlUp).Row

then plug in....
Range("R4").AutoFill Destination:=Range("R4:R" & lr)
Range("R4:R" & lr).Select

regards
FSt1

 

[Luke M]

More info please! If a column is stating FALSE, do you want to select the
column? Or the column before/after? Which rows?

 

[Don Guillett]

So, how can we help. You really need to help us to help you.