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
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.

      • CPBExcelnovice's avatar
        CPBExcelnovice
        Copper Contributor
        Oct 25, 2022
        This will seem like the dumbest question so far, but it has me baffled. I have a sub named "DynamicRange()" which determines the last row and column (so that I can dynamically determine the range that has data). It finishes up selecting the range as per the following line of code:

        'Select Range
        sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

        What I cannot work out is how to return that range to my main VBA program so that I can set the borders. My static code is:
        Sheets("Report").Range("C2:L80").BorderAround _
        ColorIndex:=25, Weight:=xlThick

        To dynamically alter the last column and row I tried the following:

        Sheets("DIT Report").Range(StartCell, sht.Cells(LastRow, LastColumn)).BorderAround _
        ColorIndex:=25, Weight:=xlThick

        It didn't work presumably because the value of the variables are not transferring from the sub routine to the main code.
        What am I missing here?

Resources