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 as a subroutine to determine the area.

 

Sub Select_All_Cells_with_Data()

Set Rng = Worksheets("Sheet1").UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

End Sub

Great, I thought...but!

 

My understanding is that  "Select_All_Cells_with_Data()" needs to contain something between the parenthesis, but being an absolute novice I have no idea!

 

My next question is having worked out the cells with data, how do I set the print area?

 

 

excel
Macros and VBA

7 Replies

Sort By
  • 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

Resources