VBA to create print range to column on right

[Shadkng]

Hi, I would like to VBA create a print range as such: From cell A3 down to a row number specified in cell A1, and then to the rightmost column that has data in it. However there will be hidden columns between column A and the last column. Thanks

 

[Shadkng]

Yes, it's working better. It's finding the range correctly now. But in the if statement where it looks to cell A1 to determine if it should fit to page, it seems to use A1 in "MASTER FORM" for all sheets. It need to look at A1 in each respective sheet to set the that page.

 

[jolivanes]

Sorry, I missed that. It is doing the right thing because there is no period before the "Range" so it uses the value from whichever sheet is active.

If Range("A1") <= 45 Then    '<----- If you use this, it needs the period as in .Range

But you don't need it like that. We have the "GT" for that so change this

If Range("A1") <= 45 Then

to this

If GT<=45 Then

 

[Shadkng]

Hi, Fantastic it works well. I ended up using the "If .Range" line because I changed the GT cell to A2 from A1 and I didn't want to mess with your code. I might come back to you down the road if I need another tweak. I always try to figure some stuff out on my own by grabbing pieces of code that I search, but this is too complicated...I am only a beginner. Thanks so much taking the time!

 

[jolivanes]

Good to hear that you have it working.
Whatever works for now should be OK.
If you want to stick with the GT idea, all you have to do is change the 1 to a 2 if you go with A2

GT = .Cells(2, 1).Value

Good luck

 

[Shadkng]

One more question related to the GT value. I am trying to sum the number of rows in a range A3 to A1000 disregarding the hidden rows and blank cells in that range. I tried subtotal function in A2 which almost worked but it didn't count the empty cells. I need this value to show in A2 which is used in the macro. This would not be a macro just a formula in A2. Would it be a find last row kind of thing and not a range? If this need to be a separate thread I can do that. Thanks

 

[jolivanes]

I wouldn't know how to do that with a formula. A new thread would be the way to go.
If you want it with code to use at the start of the current code, let us know.
BTW, where does this A1000 come from. To nice of a round number to be a real number.

 

[Shadkng]

Hi Jolivanes, is it possible with vba to sum the widths of a range of visible columns (must ignore hidden columns)? Then I want to use that number in my code as a determining factor in whether the print range should print in landscape or portrait. Since the number of columns vary in size, I am thinking this could work. Thanks

 

[jolivanes]

If your last column to be printed is Column J (Column # 10), all you have to use is

Cells(1, 11).Left    '<----- equals the left of the next Column = Column K = Column # 11

It does not count the widths of hidden Columns.

If one is in Points and the other one is in Pixels you need to convert.
I do not know if that is the case though.

 

[Shadkng]

I have 26 columns and any number of them can get hidden, so I would have to use 27 as the column number? But what if only 6 columns are visible.

Is there a way you could output the total width in a message box somehow so I could see the number?

 

[jolivanes]

What do you get if you'd tried

MsgBox Cells(1, 27).Left