How can I rebuild a Range from an R1C1 address?

Toddleson 0
Viewed 25

I have a macro which looks at the user-defined print area, and then makes some adjustments to the Page Setup based on the print area. The problem I am running into is that some users like R1C1 notation, and when their application is set to R1C1, the print area is given as an R1C1 address. This is breaking my code, because I turn the print area back into a range in order to analyze it in my macro.

For example:

With SDS.PageSetup
    If SDS.Range(.PrintArea).Rows.Count >= 53 Then .FitToPagesTall = 2 Else .FitToPagesTall = 1
End With

This works if the user is using A1 Notation, but breaks if the user has R1C1 notation because .PrintArea returns an R1C1 address and then Range fails to create an object.

How can I change this and similar lines in my code to be compatible with R1C1 notation? Or how can I have .PrintArea return A1 notation regardless of the user's settings?

As a testing example, my print area at the time I encountered this error was "R23C2:R125C12,R23C14:R125C24" in R1C1 and "$B$23:$L$125,$N$23:$X$125" in A1.

vba excel
1 Answers

Thank you @GSerg!

I was able to use Application.ConvertFormula to ensure that the .PrintArea always ends up in A1 reference style regardless of the user's current setting. I check the user's reference style with Application.ReferenceStyle and use that as the argument for FromReferenceStyle in ConvertFormula

Here is the corrected example:

With SDS.PageSetup
    Dim PrintPages As Range
    Set PrintPages = SDS.Range(Application.ConvertFormula(.PrintArea, Application.ReferenceStyle, xlA1))
    If PrintPages.Rows.Count >= 53 Then .FitToPagesTall = 2 Else .FitToPagesTall = 1
End With

To ensure full compatibility, I went through my modules and made sure that every instance of Range(.PrintArea) included ConvertFormula, similar to the above example.

Toddleson0
Related