Set print area of used cells macro

[JamesMook]

Hi, I have done a search of the forums and have found a few macros to set print area to only include used cells however they are not quite doing what i want.

I have a menu panel in cells A1 down to A4 and across to cells N1 and again down to N4.

I want the set print area macro to set from cells C5 across to N5 and then downwards to the last filled cell. I have borders around cells down to 1152 and some of these cells include formulas so need these not to be affected.

This macro will run from a button in the menu section so don't want an autorun macro button.

Hope this makes sense but if not please ask and i shall try to clarify. can anyone help?

 

[Domenic]

Does this do what you want?

With Range("C5:N" & Rows.Count)
    LastRow = .Find(what:="*", after:=.Cells(1), LookIn:=xlValues, Lookat:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End With

ActiveSheet.PageSetup.PrintArea = Range("C5:N" & LastRow).Address

 

[JamesMook]

Does this do what you want?

With Range("C5:N" & Rows.Count)
    LastRow = .Find(what:="*", after:=.Cells(1), LookIn:=xlValues, Lookat:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End With

ActiveSheet.PageSetup.PrintArea = Range("C5:N" & LastRow).Address

Excellent and yes it does. It's been doing my nut for days. lol Thanks

 

[Domenic]

You're very welcome!