none
Adding several print areas to a worksheet RRS feed

  • Question

  • Hi,

    I am trying to create a script that sets a print area depending on the contents of the page. The page contains 40 ranges that could be part of the print area if a part of the range contain text. that means the print areas could be 1 up to 40 ranges.

    the probelm is that i only get up to 19 ranges in the print area. I think this is because the range property can only contain 250 characters but I am not sure.

    the code so far:

    '**********************

    Sub PrinterFriendlyEtiketter()
    ActiveSheet.PageSetup.PrintArea = ""
    Dim MultipleRange As Range
    Dim MultipleRange2 As String

    startrad = 10
    startcol = 16
    radavstand = 7
    colavstand = 10
    k = 1

    Set MultipleRange = Range(Cells(10, 16), Cells(15, 24))

    For i = 0 To 19
    For j = 0 To 3

    arad = startrad + i * radavstand
    acol = startcol + j * colavstand

    If Cells(arad, acol) <> "" Then
    Set r1 = Range(Cells(arad, acol), Cells(arad + 5, acol + 8))
    Set MultipleRange = Union(MultipleRange, r1)
    End If

    Next j
    Next i
    MultipleRange.Select

    ActiveSheet.PageSetup.PrintArea = Selection.Address

    end sub

    '**********************

    is there a way to add more ranges to print area? either from the start or to add new print areas to a worksheet that already contains several printareas?

    many thanks

    Monday, May 28, 2012 8:38 AM

Answers

  • My Experiment also returns same result.I think there is a limitation of 250 char for PrintArea.Even I tried with A1,C1,E1 etc and see that when the total charecter crossed 250 it stops.However I found 43 areas becasue my address was small a1 and yours was P10:X15.

    However I have given a workaround which will add a sheet and give you different pages for printing.Adjust the spaces becuase the browser has shifted some one liners to 2 lines

    Sub PrinterFriendlyEtiketter()

    Dim MultipleRange As Range
    Dim MultipleRange2 As String
    Dim wsOldSheet As Worksheet

    startrad = 10
    startcol = 16
    radavstand = 7
    colavstand = 10
    k = 1
    c = 1

    Application.ScreenUpdating = False

    Set wsOldSheet = ActiveSheet
    Worksheets.Add

    With wsOldSheet
        Set MultipleRange = Range(.Cells(10, 16), .Cells(15, 24))

        For i = 0 To 19
            For j = 0 To 3
                arad = startrad + i * radavstand
                acol = startcol + j * colavstand
                If .Cells(arad, acol) <> "" Then
                    Range(.Cells(arad, acol), .Cells(arad + 5, acol + 8)).Copy _
                        Range("a" & c)
                    If c >= 5 Then
                        ActiveSheet.HPageBreaks.Add Range("a" & c)
                    End If
                    c = c + 5
                End If
            Next j
       Next i

    End With

    Application.ScreenUpdating = True

    End Sub

     

    Monday, May 28, 2012 12:10 PM
    Answerer

All replies

  • Hi,

    I am trying to create a script that sets a print area depending on the contents of the page. The page contains 40 ranges that could be part of the print area if a part of the range contain text. that means the print areas could be 1 up to 40 ranges.

    the probelm is that i only get up to 19 ranges in the print area. I think this is because the range property can only contain 250 characters but I am not sure.

    the code so far:

    '**********************

    Sub PrinterFriendlyEtiketter()
    ActiveSheet.PageSetup.PrintArea = ""
    Dim MultipleRange As Range
    Dim MultipleRange2 As String

    startrad = 10
    startcol = 16
    radavstand = 7
    colavstand = 10
    k = 1

    Set MultipleRange = Range(Cells(10, 16), Cells(15, 24))

    For i = 0 To 19
    For j = 0 To 3

    arad = startrad + i * radavstand
    acol = startcol + j * colavstand

    If Cells(arad, acol) <> "" Then
    Set r1 = Range(Cells(arad, acol), Cells(arad + 5, acol + 8))
    Set MultipleRange = Union(MultipleRange, r1)
    End If

    Next j
    Next i
    MultipleRange.Select

    ActiveSheet.PageSetup.PrintArea = Selection.Address

    end sub

    '**********************

    is there a way to add more ranges to print area? either from the start or to add new print areas to a worksheet that already contains several printareas?

    many thanks

    Monday, May 28, 2012 8:21 AM
  • This is the forum for vb in visual studio, try the forum for VBA instead of this.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


    Success
    Cor

    Monday, May 28, 2012 8:27 AM
  • sorry.
    Monday, May 28, 2012 8:37 AM
  • The PrintArea can be set to non contagious ranges.Not by addition or deletion,simply assigning to the address.

    But even if you select some range which are actually contagious but you select different times.Excel will convert to 1 area.

    Suppose you selected A1,c1 then B1 there will not be three area.Excel will created one print area A1:C1 not a1,b1,c1.

    I suppose you need to add one range to print area if it has some content.But not understood the reason of Mulitiplication and union etc.

    Explain a bit detail by commenting and we could help much better.

    Monday, May 28, 2012 10:15 AM
    Answerer
  • I have a page which consists of 40 labels that are filled out using a different script. when I want to print the labels I only want the ones that has been filled out to be part of the print area. I understand that there is really only one printarea and the ranges are only different parts of it.

    I want the labels to be printed on separate pages.

    Basically, I have a grid of labels, 20 rows and 4 columns. each label is separated by one row and one column.

    ex. label one is range P10:X15 and label two is range Z10:AH15. Then it continues like that.

    If the first row of each the label has some text in it, then that label should be added to the print area. It works fine up to 19 different pages within the print area but then it just stops. When checking the length of MultipleRange.Address it shows that it is exactly 250 characters long. I am not sure but I think the problem might be that the range cannot contain more than 250 characters.

    Monday, May 28, 2012 10:58 AM
  • My Experiment also returns same result.I think there is a limitation of 250 char for PrintArea.Even I tried with A1,C1,E1 etc and see that when the total charecter crossed 250 it stops.However I found 43 areas becasue my address was small a1 and yours was P10:X15.

    However I have given a workaround which will add a sheet and give you different pages for printing.Adjust the spaces becuase the browser has shifted some one liners to 2 lines

    Sub PrinterFriendlyEtiketter()

    Dim MultipleRange As Range
    Dim MultipleRange2 As String
    Dim wsOldSheet As Worksheet

    startrad = 10
    startcol = 16
    radavstand = 7
    colavstand = 10
    k = 1
    c = 1

    Application.ScreenUpdating = False

    Set wsOldSheet = ActiveSheet
    Worksheets.Add

    With wsOldSheet
        Set MultipleRange = Range(.Cells(10, 16), .Cells(15, 24))

        For i = 0 To 19
            For j = 0 To 3
                arad = startrad + i * radavstand
                acol = startcol + j * colavstand
                If .Cells(arad, acol) <> "" Then
                    Range(.Cells(arad, acol), .Cells(arad + 5, acol + 8)).Copy _
                        Range("a" & c)
                    If c >= 5 Then
                        ActiveSheet.HPageBreaks.Add Range("a" & c)
                    End If
                    c = c + 5
                End If
            Next j
       Next i

    End With

    Application.ScreenUpdating = True

    End Sub

     

    Monday, May 28, 2012 12:10 PM
    Answerer
  • Thanks. It was a really good idea and with some modifications it works perfectly.
    Monday, May 28, 2012 2:08 PM