Macro to set print area to 60 rows total (excluding filtered hidden rows)

[Ruthanne]

Hello pros,
Thank you for all your help & time & smarts!
I have not found a similar thread to help on this issue. I am trying to write a macro to size print area for the resulting "unhidden" rows in an engineering drawing that has requirements for size, borders, margins & appearance.

I currently have a print area set to rows 1-76 & columns AA-BD. I have a border around the entire worksheet except for one row & column around the outside of the border for margin. This border & margin is a "standard" requirement and my "issue": The problem is that when many of the rows get hidden by a macro that filters out the "non-applicable" data in those rows, the result is (for example) that only about 25 rows of the 76 are not hidden, so then the border is no longer around the "edge" of the worksheet but is smooshed down.

My approach to the solution is to have a macro that will insert enough blank rows above row 72 to total 60 "non-hidden" rows in the final print area (I think adding rows is the right approach because it will keep the overall "scale" of the text & worksheet the same as the other worksheets.

By the way, this is the last hurdle I have to get over in this 3-4 year project/file that is automating a previously long & tedious drawing process...& all of the macros & formulas I owe to this forum! You all are wonderful!
Thank you in advance for any bones you can throw me!

 

[Jerry Sullivan]

Hi Ruthanne,

Congratulations on nearing the home stretch of your long project.

This code should work to add the rows you need based on the current setup that you describe:

Sub PadRows()
    Dim lngRowsVisible As Long, lngRowsDiff As Long
    Application.ScreenUpdating = False
    With ActiveSheet.Range("AA1:AA76")
        lngRowsVisible = .SpecialCells(xlCellTypeVisible).Count
        lngRowsDiff = 60 - lngRowsVisible
        If lngRowsDiff > 0 Then
            .Range("AA72").Resize(lngRowsDiff).EntireRow.Insert
        End If
    End With
End Sub

If you have a need to go the other direction (say you change your filter and now have to delete rows to get to 60 total), this could be modified a bit. The code would probably read the PrintArea, since the border will no longer be at Row 76. Just let me know if you want some help doing that.

 

[Ruthanne]

Bless your big Excel heart JS411! I will plug this in!
...and cheers to you! (I got a little sad yesterday with "no replies"....you saved the project!)...another cheers!!!

 

[Ruthanne]

Hello all, I am hoping someone can help me embellish this macro that JS411 was so kind to write for me.
I think the change that would work for my situation is to add VBA at the beginning of the macro JS4100 wrote for me that will delete any "non-hidden" blank rows between 71 & 100. (I already had a macro that filters & hides and "non-applicable" data between rows 2 & 71.
I'm not sure how to read the code of the above formula but each time I run it, it adds more & more rows. My goal is to end up with 60 "non-hidden" rows total (and also, by the way) without deleting the drawing number & data in row 72 through 76.

...and good timing for me, I see JS411 just come online. Thank you so much for the original help and formula!

Thank you all for all the valuable help!

 

[Sektor]

...that JS411 was so kind to write for me.....
...beginning of the macro JS4100 wrote....

Is this the same man?

 

[Ruthanne]

I'm 89% sure it is...good catch!

 

[Jerry Sullivan]

Hi Ruthanne,

Nice to hear from you again.

I had a feeling you would want to go the opposite direction some day...

If you have a need to go the other direction (say you change your filter and now have to delete rows to get to 60 total), this could be modified a bit. The code would probably read the PrintArea, since the border will no longer be at Row 76. Just let me know if you want some help doing that.

I'd be glad to help with the modification. I need to think a little about the best way to do that....seems a waste to delete rows and then add them.

I'll post a solution a little later tonight unless someone else gets to that ahead of me.

Quote:
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Originally Posted by Ruthanne
...that JS411 was so kind to write for me.....
...beginning of the macro JS4100 wrote....

</TD></TR></TBODY></TABLE>
Is this the same man?

I assumed Ruthanne was giving me a promotion in rank!

<!-- / message -->

 

[Ruthanne]

Thank you so much JS411...or should I just call you Jerry to be safe!
You are very kind. I am most grateful for your help!

 

[Jerry Sullivan]

I think the change that would work for my situation is to add VBA at the beginning of the macro JS4100 wrote for me that will delete any "non-hidden" blank rows between 71 & 100.

Ruthanne,

I've been pondering whether there are any scenarios in which the process you describe wouldn't give the desired result.

There are two variables: Count of Visible Rows in your Print Area, and Row Numbers that your 4 Rows of Title space are currently located.

Below are the 9 combinations of possible scenarios. (Okay- I admit I'm a nerd ).

Excel Workbook
	B	C	D	E
1		Count of Visible Rows in Print Area
2	Rows of Title	Less than 60	60	Greater than 60
3	On rows 72-76	Insert Rows at Row 72-Original	Do nothing	Delete Blank Rows above Row 72
4	Above rows 72-76	Insert Rows above first Row of Title - Modified	Do nothing	Delete Blank Rows above first Row of Title
5	Below rows 72-76	Insert Rows above first Row of Title - Modified	Do nothing	Delete Blank Rows above first Row of Title
Sheet1

Excel 2007

So the scenarios in Green are the ones we already have covered with the Original Macro. The one in Yellow is the one that your proposed revision would address.

Do the remaining four possibilities in White ever occur in your process? If so they would need to handled differently (perhaps reading the print area address).

 

[Ruthanne]

Hello Lifesaver!
I think the answer is no, that I will never have the other 4 white scenarios because I will have a protected worksheet that does not allow users to insert or delete rows; the worksheet will have "drawing notes" & a heading on rows 1-71 & the drawing title on rows 72-76. A previous macro will filter then hide rows of all the non-applicable notes & the end result usually leaves about 16 to 50 rows "non-hidden"....but these notes start pre-established in rows 1-71 for every workbook.
If it is wiser to have the macro look at the print area, that should work fine too...I guess..!?!
You are dynomite!!!!! Thanks for the help!