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]

When deleting "blank" visible rows, which cells need to be tested to determine a row is blank? Do we need to check the entire range of AA:BD for that row?

Is there a chance there is something on that row between A:Z that we don't want to delete?

 

[Jerry Sullivan]

This should work. Make sure to copy all code (there is a Sub and a Function)

Sub Pad_or_Delete_Rows()
    Dim lngRowsVisible As Long, lngRowsDiff As Long
    Dim lngRowPrintBottom As Long, lngRowTest As Long
    Application.ScreenUpdating = False
 
    With Range(ActiveSheet.PageSetup.PrintArea)
        lngRowPrintBottom = .Rows.Count
        lngRowsVisible = .Resize(, 1) _
            .SpecialCells(xlCellTypeVisible).Count
        lngRowsDiff = 60 - lngRowsVisible
        Select Case lngRowsDiff
            Case Is > 0
                 .Cells(.Rows.Count - 4, 1).Resize(lngRowsDiff) _
                         .EntireRow.Insert
            Case Is < 0
               ' Delete visible blank rows beginning at
               ' .Cells(.Rows.Count - 5) and going up until a total of
               ' lngRowsDiff rows have been deleted or Row 71 reached
                 For lngRowTest = .Cells.Rows.Count - 5 To 72 Step -1
                    If Not .Cells(lngRowTest, 1).EntireRow.Hidden And _
                        isBlankRow(.Cells(lngRowTest, 1)) Then
                        .Cells(lngRowTest, 1).EntireRow.Delete
                        lngRowsDiff = lngRowsDiff + 1
                        If lngRowsDiff = 0 Then Exit For
                    End If
                 Next lngRowTest
        End Select
    End With
End Sub
 
Private Function isBlankRow(c As Range) As Boolean
    On Error Resume Next
    isBlankRow = c.EntireRow.Find("*") Is Nothing
End Function

This checks the entire row and doesn't delete it unless all cells in the row are blank.

 

[Ruthanne]

Well...I never want any rows between 1-71 deleted; they are only hidden by previous macro. On my test worksheet (various rows from 1-71 are hidden), after running your original macro (that inserts rows "before" my drawing title rows of 72-76), my drawing title rows now are on row 100-104). so I guess the macro could say:
delete any rows after row 71 that are blank; then insert enough rows before row 72 to end up with 60 rows total "non-hidden".
Does that make sense? (that was the only glitch with your original macro...it kept adding more rows...I don't think it could identify the total of 60 showing to include the drawing title in row 72-76).

Wow you are fast...I just saw that you have pasted another macro. I will try that & report back! I'm in Japan so it's NOT midnight my time but I sure appreciate you helping so late!

 

[Ruthanne]

Yippee! It worked and I owe it all to you JS411! I did one little tweak since it was adding the rows after row 72 & not before (I changed the "Count - 4" to "Count - 5" [and that's just for the record in case someone is using the code in the future]).
Well, thanks so much...for burning the midnight oil for me...this was the last "blip" in automating this workbook. My co-worker back home just told me he is training folks tomorrow how to use it so I do appreciate the fast help!
Yippity Skippity! & sincere thanks!

 

[Jerry Sullivan]

Delighted to help you Ruthanne. Hope you enjoy the rest of your trip!