Macro to copy a range that is constantly changing

Posted by Joe on May 22, 2001 9:27 AM

I have several columns and rows of formulas that I want to "copy" and "paste special" their values.
The problem is, the number of rows vary up and down but the macro I've written selects the same
number of rows each time. Basically it assigned a range and wants to use the same range each time.
I want the range to be variable based on the number of rows I'm targeting. In the old days, I would
simply anchor the cursor and use "end up" and "end right" to highlight the range I wanted to copy.

Posted by Tuc on May 22, 2001 9:32 AM

Hi Joe!

Can you identify the range you want to select as a named range and just copy that instead? One caveat, you want to be careful inserting/deleting with a named range when ever you insert or delete along the "edges". If the row/colum you are inserting is not between the top/bottom/right/left boundaries the insert might not be considered to be part of the named range you are dealing with.

Just a thought,

Tuc

Posted by Kevin James on May 22, 2001 1:14 PM

copying changing range

Hi Joe,

I just learned that the other day while reading my copy of "Excel2000 Programming for Dummies." This will work in Excel97 as well.

Redo you macro, but this time select a range (any range) BEFORE starting the macro. Then start the recording.

In the example written in the book (by John Walkenbach), he was illustrating converting all formulas to static info (similar in function to Paste Special).

Seeing as he had already selected the range before starting the recording of the macro, the first line in the code read:

Selection.Copy

Note that no range is specified. It is probably illegal for me to reproduce the rest of the code here, but this should be enough of a clue to get your started.

Kevin

Posted by Kevin James on May 22, 2001 1:16 PM

one more thing

Almost forgot...

So the next time you need to USE your macro, you would first select the range you want and THEN run the macro.

Kev

Posted by Dave Hawley on May 22, 2001 2:22 PM

Hi Joe

Here is how you can copy a variable range. If it's not quite what you need, give me some more details and I'll modify it.

Sub TryThis()
'Copy to last row
Range("A1", Range("A65536").End(xlUp)).Copy
End Sub

Sub TryThis()
'Copy to first blank row
Range("A1", Range("A1").End(xlDown)).Copy
End Sub

Dave

OzGrid Business Applications