print range macro with last blank row

[vbnoob]

Hi all,

Long story: I have got a macro which prints top level detail summary and with the summary of each project. At the moment the Macro is static and only goes down to row 98. However if I add in a project which will be one line for summary and say about 8 detail lines (which are grouped and hidden under the macro) it will go down to line 107 etc. and wont print that new line.

Short Story: How can I change the following macro so it picks the last blank row and not just a specific line.

Sub Print_initative()
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.PageSetup.PrintArea = "$B$3:$AI$93"
With ActiveSheet.PageSetup
Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 2
            .PrintGridlines = False
            .LeftMargin = Application.InchesToPoints(0.1)
            .RightMargin = Application.InchesToPoints(0.1)
            .TopMargin = Application.InchesToPoints(0.1)
            .BottomMargin = Application.InchesToPoints(0.1)
            .CenterHorizontally = True
            End With
            Application.Dialogs(xlDialogPrint).Show

This will be for non VB users so dont want them to have to change code all the time.

Thanks for the help guys, this board is a life saver!

 

[Mister H]

Hi vbnoob:

I think if you just set up your Print Area on the spreadsheet (not using the macro) then when you insert rows your Print Area should increase. You of course have to insert the rows within the existing Print Area. I think you could also just set up the margins and print details in File/Page Setup and then all you have to do is click the print button as no macro would be required). Anyway I am not certain of your requirements at your end but i thought I would give you my 2 cents worth.

Bye 4 Now,
Mark

 

[vbnoob]

Hi,

I have to use a macro to do it because the button does a bit more than that and also its the way we want them to print as it needs to be a report at board level.

For anyone that is interested I have copied the code below which worked

Sub Print_initative()
ActiveSheet.Outline.ShowLevels RowLevels:=1
'new code
Start = Range("B3").Address
Range("AJ17").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlUp).Select
Finish = ActiveCell.Offset(1, 0).Address
myPrintArea = Start & ":" & Finish
ActiveSheet.PageSetup.PrintArea = myPrintArea
With ActiveSheet.PageSetup
Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 2
            .PrintGridlines = False
            .LeftMargin = Application.InchesToPoints(0.1)
            .RightMargin = Application.InchesToPoints(0.1)
            .TopMargin = Application.InchesToPoints(0.1)
            .BottomMargin = Application.InchesToPoints(0.1)
            .CenterHorizontally = True
            End With
            Application.Dialogs(xlDialogPrint).Show
            
            
        Sheets("PP").Select
        Range("G5:L5").Select
End Sub

 

[Mister H]

Glad to hear you got it working...