Forum Discussion

CPBExcelnovice's avatar
CPBExcelnovice
Copper Contributor
Oct 18, 2022

VBA Calling a Macro - So basic and yet difficult for a novice!

I have a spreadsheet that has a variable number of rows of data and I want to select the area and set it as the new print area.   After looking around I came across the following code which can run...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Oct 18, 2022

CPBExcelnovice 

There doesn't need to be anything between the parentheses. In fact, a macro is a procedure / sub that doesn't have any arguments.

The macro that you posted is horribly inefficient and it is probably not suitable for your purpose: it selects only the non-empty cells in the used range, resulting in a non-contiguous selection. If you set that as print area, you'll get as many pages in the print out as separate areas in the selection.

 

Can you explain in more detail how you want to set the print area? Thanks in advance.

CPBExcelnovice's avatar
CPBExcelnovice
Copper Contributor
Oct 20, 2022

Thanks for your replyHansVogelaar I'm sure that what I am doing is far from efficient, but I figure each step I take I learn a little bit more.

 

Yes, I finally worked out that a Macro is simply called using 'Call' Name_of_Macro .... Step 1 learned!

 

I have a Macro which copies cells from a worksheet into a new worksheet, however the number of rows varies.  So I'm trying to find the range, set a border around the range and then set the print parameters i.e. set print range, set page to Landscape, fit all columns to 1 page.   But I don't want to actually print the page, it just means that if my boss wants to all of the donkey work is done for him.

 

 

 

 

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 20, 2022

    CPBExcelnovice

    You only need to set the Print Area if you don't want to print all contents of a worksheet. See if this does what you want:

    Sub SetPrintProperties()
    ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
    End With
    Application.PrintCommunication = True
End Sub
    • CPBExcelnovice's avatar
      CPBExcelnovice
      Copper Contributor
      Oct 24, 2022
      Thanks for your help. I messed around with the code for a while but couldn't quite force it to cooperate!

      The code puts lines around all of the cells, rather than around the border of the selected range.

      I changed
      ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
      to
      ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous
      which worked.
      But I then got clever and tried to add a line weight by adding
      , Weight:=xlThick
      but the code was rejected.



      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Oct 25, 2022

        CPBExcelnovice 

        That should work, if you are adding the comma and named Weight parameter on the same line.  If you are splitting it to another line, you need to include continuation characters (a space and underscore) at the end of the first line:

            ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous _
            , Weight:=xlThick
    '...I prefer that style of splitting, but others prefer:
    ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous, _
            Weight:=xlThick

         You can continue a statement on up to 10 lines, IIRC; include continuation characters on all but the last line.

Resources