• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to combine dynamic and fixed ranges to set print area

G

GioP

New Member
Could you please help me to combine dynamic range and fixed range in the print area?


I used the VBA code to set print area for dynamic range.

The table goes from A1 to max M313.


In addition I need to add into printing area fixed range(“A315:M321”)


Here is the code:


Private Sub CommandButton1_Click()

Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

ActiveSheet.PageSetup.PrintArea = "A1:M" & LastRow

End With

End Sub


Your swift reply to fix this VBA for command button is highly appreciated


GioP
 
Hi GioP ,


Can you check ou this link , and see if it helps ?


http://www.mrexcel.com/forum/showthread.php?434743-VBA-Code-for-Print-Area-of-non-contiguous-ranges


Narayan
 
Thanks for link, however it works in vertical and in my case I need to set horizontally the print areas.

As I mentioned in the table range(“A1:M313”) dynamic range will be changing reaching max row 313. Static range(“A315:M321”) should be added be included after area for dynamic range is set.


Any suggestion how to fix this VBA ?


GioP
 
Hi ,


Does the following line not work ?


ActiveSheet.PageSetup.PrintArea = "$A$1:$E$" & LastRow & ",$K$1:$AE$" & LastRow


The two ranges are mentioned there , separated by a comma.


In your case , have you tried :


ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & LastRow & ",$A$315:$M$321"


Narayan
 
It works but range (“A315:M321”) goes to a new page. Seams there is a page break in between. How to eliminate this part?


Thanks a lot and regards
 
Hi GioP ,


This has already been answered in other forums ; check out this link :


http://www.erlandsendata.no/english/index.php?d=envbaprintmultirange


Narayan
 
I see this is the reference


Range("CauseCols").EntireColumn.Hidden = True


How to define range for blank rowes between dynamic and fixed ranges.

In other words to determine "CauseCols" range


Thanks


If you help me to fix then VBA comes complete
 
Hi GioP ,


See the code below , taken from the following link :


http://www.erlandsendata.no/english/index.php?d=envbaprintmultirange

Sub PrintSelectedCells()
' prints selected cells, use from a toolbar button or a menu
Dim aCount As Integer, cCount As Integer, rCount As Integer
Dim i As Integer, j As Long, aRange As String
Dim rHeight() As Single, cWidth() As Single
Dim AWB As Workbook, NWB As Workbook
Dim addresses_array() As String

Dim r1 As Range, r2 As Range

If UCase(TypeName(ActiveSheet)) <> "WORKSHEET" Then Exit Sub
' useful only in worksheets
aCount = Selection.Areas.Count
If aCount = 0 Then Exit Sub ' no cells selected
cCount = Selection.Areas(1).Cells.Count

If aCount > 1 Then ' multiple areas selected
Application.ScreenUpdating = False
Application.StatusBar = "Printing " & aCount & " selected areas..."
Set AWB = ActiveWorkbook
rCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
cCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
ReDim rHeight(rCount)
ReDim cWidth(cCount)
ReDim addresses_array(1 To aCount)

For i = 1 To aCount
addresses_array(i) = Selection.Areas(i).Address
Next

For i = 1 To rCount
' find the row height of every row in the selection
rHeight(i) = Rows(i).RowHeight
Next i

For i = 1 To cCount
' find the column width of every column in the selection
cWidth(i) = Columns(i).ColumnWidth
Next i

Set NWB = Workbooks.Add ' create a new workbook

For i = 1 To rCount ' set row heights
Rows(i).RowHeight = rHeight(i)
Next i

For i = 1 To cCount ' set column widths
Columns(i).ColumnWidth = cWidth(i)
Next i

For i = 1 To aCount
AWB.Activate
aRange = addresses_array(i)
Range(aRange).Copy ' copying the range
NWB.Activate
With ActiveCell ' pastes values and formats
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
ActiveCell.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Next i

NWB.PrintOut
NWB.Close False ' close the temporary workbook without saving
Application.StatusBar = False
AWB.Activate
Set AWB = Nothing
Set NWB = Nothing
Else
If cCount < 10 Then ' less than 10 cells selected
If MsgBox("Are you sure you want to print " & _
cCount & " selected cells ?", _
vbQuestion + vbYesNo, "Print celected cells") = vbNo Then Exit Sub
End If
Selection.PrintOut
End If
End Sub

Private Sub CommandButton1_Click()
Dim Range_1 As Range
Dim Range_2 As Range
Dim LastRow As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row

Set Range_1 = Range("$A$1:$M$" & LastRow)
Set Range_2 = Range("$A$315:$M$321")

Union(Range_1, Range_2).Select
Call PrintSelectedCells
End Sub

The procedure called CommandButton1_Click is a sort of test procedure , where I am concatenating two non-contiguous ranges ; in your case Range_1 will be the dynamic table ; Range_2 is the fixed range to be added to the end of the dynamic table.


The PrintSelectedCells procedure expects you to have already selected the print area before it is called ; this is the reason for the statement :


Union(Range_1, Range_2).Select


Let me know if you have any problem.


Narayan
 
Hi,


I don´t know much about VBA, but i´m trying to combine 8 dynamic print areas from 1 sheet in one printed page (if it fits the page!).

Can´t put the file here, but i´m asking for help in this forum also: h**p://www.ozgrid.com/forum/showthread.php?t=176700


Thanks in advance!

Luís


Sorry I clicked send 3 times...
 
You must log in or register to reply here.
Back
Top