using a cell reference in a range command in VBA

[Bickerton]

Hi everyone,

I am new on here and have a small problem. I want a command button to copy a range of cells and past somewhere else on the sheet. Simple enough I know, but if I have range("A1:A10") no problem, but I want range(A1:A&H1). I know this won't work, but I want the value to change to whatever i type in H1 to change the length of the range of cells. Hope that makes sense.
Thanks
Gavin

 

[Fluff]

Hi & welcome to MrExcel.
How about

Range ("A1:A" & Range("H1").Value)

 

[Bickerton]

Thanks Fluff, that worked straight away, you are a legend!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Bickerton]

Sorry Fluff (or anyone else!), I have another question! I want to referance a cell in both parts of the range, but it keeps returning an error now using this code: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Range("C" & Range("H2").Value):C" & Range("j32").Value).Select Is this possible or will I have to find another workaround? I also want to add 1 to the value in H2, not sure if this is possible or just do it in another cell on the spreadsheet?[/FONT]

 

[Fluff]

Try

Range("C" & Range("H2").Value + 1 & ":C" & Range("j32").Value).Select

 

[Bickerton]

Hi Fluff,
Once again many thanks, worked straight away!

 

[Fluff]

Glad to help & thanks for the feedback