Using Named Cells to Define a Print Range

[jdmaybee]

I use Excel to import a recievables report from another piece of software. As you can appreciate it is not the same each time I import it.

I have written a macro that does everything I want it to except define a range using a range name I define as Topleft (for say, cell A240) and Bottomright (for say, cell M820)

I cannot seem to find the correct syntax for this since Excel seems to alway want to use finite terms and the range between Topleft and Bottomright can vary each time I import the file.

 

[dan_l]

?


I'm not following: you're just trying to define a range between A240 and M820?


I guess my question is: which of the following best describes what you're trying to do?


A. Define a range based on user provided details

B. Define a range based on criteria within the data

 

[jdmaybee]

It's a bit more difficult than that - that's why I named the two ranges I want to use to define the area - one day it will be A240 to M820 - the next it will A320 to M900. I want to be able to define a print range using the Topleft and Bottomright range names.

 

[Nunes]

using the .RefersTo property, you can extract a string with the Range inside you named ranges, but it extracts the complete address of the range, like "sheet!$A$1"...


simple string management can get you rid of the "sheet!" part of the string, and the sky is now the limit! :D


something like this:


______________________________


Dim aux_string, new_Topleft, new_Bottomright As String


aux_string = ThisWorkbook.Names("Topleft").RefersTo

'aux_string is now a string with complete address of range "Topleft"

'we should now get rid of the sheet reference in the left part of this

new_Topleft = Right(aux_string, Len(aux_string) - InStr(aux_string, "!"))

'done! new_Topleft is a string with simple cell address of range "Topleft"


aux_string = ThisWorkbook.Names("Bottomright").RefersTo

'aux_string is now a string with complete address of range "Bottomright"

'we should now get rid of the sheet reference in the left part of this

new_Bottomright = Right(aux_string, Len(aux_string) - InStr(aux_string, "!"))

'done! new_Topleft is a string with simple cell address of range "Bottomright"


'now we apply it to the print area

ActiveSheet.PageSetup.PrintArea = new_Topleft & ":" & new_Bottomright

______________________________

 

[jdmaybee]

Wow! Does that ever work!!

As an old Lotus user I could literally write a macro by copying all the key strokes I used and bingo I had a macro.

I had the (what turns out to be very unrealistic) idea I could figure out how to do this on my own!

Just out of curiosity do you know how to name a range without having the RefersToR1C1:="=Sheet3!R8C1" part at the end? I want to be able to select the ActiveCell name a range.


Thanks again