using cell values to select range of cells

[Ray]

I need to select a range of cells for copying/pasting
opperations but the range changes everytime new data is
imported into the worksheet. The 2 columns remain the
same but the rows vary. I can calculate the upper left
row number and store that value in a specific cell
location and likewise I can calculate the bottom right
row number and store that value in another specific cell
location, but I don't know how to write the macro code to
assign the values. ex: [range("BA$:CX#").select]
(where "$"= the value in cell AY1 and "#"= the value in
cell AY2. It probably is very simple but I keep getting
syntax errors.

 

[pfsardella]

The values are stored in Cells AY1 and AY2. Either syntax will work.

Range("BA" & [AY1] & ":CX" & [AY2]).Copy
Range("BA" & Range("AY1").Value & ":CX" & Range("AY2").Value).Copy

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul

 

[Raymond Baker]

Thank you for your response. Your code worked fine as long as there were
no values in ay1 or az1 but as soon as values were enytered and the
macro was run I got a "Run-time error '1004': Method'Range'of
Object'_Global'Failed" error message. Any suggestions? Thanks -- Ray



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[pfsardella]

The only way that I could reproduce this error message was by having
one value (or both) greater than 65536 (the maximum number of rows).

HTH
Paul

 

[Raymond Baker]

My fault, I had the wrong cell typed in the code. Thanks much. It works
great! Ray



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!