Print Area Dinamis di Macro Excel

Jika kita membuat macro dan kita ingin print area dinamis sesuai range yang kita inginkan kita bisa menggunakan code seperti di bawah ini :

Dim totrw = 1

Do

If Cells(totrw, 8).Value = ” (AUTHORISED SIGNATURE)” Then exit do

totrw = totrw + 1

Loop

totrw ada total row yang sudah kita looping sebelumnya

Range(Cells(1, 1), Cells(totrw, 10)).Select

ActiveSheet.PageSetup.PrintArea = Selection.Address

 

code lengkapnya seperti ini :

Global kelInv As Boolean

Sub invoice()
frmInv.Show
‘===== JIKA DIBATALKAN MAKA KELUAR DARI FORM ========
If kelInv = True Then GoTo finish
‘===== SET EXCEL APPLICATION =====
Set ws = Windows.Application.ActiveSheet
sname = ws.Name
Application.DisplayAlerts = False

Cells.Select
Selection.RowHeight = 17
With Selection.Font
.Name = “Courier New”
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

totrw = 1
Do
If Cells(totrw, 8).Value = ” (AUTHORISED SIGNATURE)” Then
Cells(totrw – 1, 8).Value = “———————–”
Exit Do
End If

If Cells(totrw, 5).Value = “INVOICE    ” Then
Cells(totrw, 5).Select
With Selection.Font
.Name = “Courier New”
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
End If

If Cells(totrw, 7).Value = “DOC NO.” Then
Cells(totrw, 6).Value = Cells(totrw, 7).Value
Cells(totrw, 7).Select
Selection.ClearContents
Cells(totrw, 8).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End If

If Cells(totrw, 7).Value = “VAT REG#:” Then
Cells(totrw, 6).Value = Cells(totrw, 7).Value
Cells(totrw, 7).Select
Selection.ClearContents
Cells(totrw, 7).Value = Cells(totrw, 8).Value
Cells(totrw, 8).Select
Selection.ClearContents
End If

If totrw = 56 Then
Cells(totrw, 1).Select
Selection.EntireRow.Insert
totrw = totrw + 1
ElseIf totrw > 56 Then
If Cells(totrw, 5).Value = “TO  BE  CONTINUED” Then
Cells(totrw + 1, 1).Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
totrw = totrw + 2
End If
End If
totrw = totrw + 1
Loop
‘====cell akhir height jadi 12 ===========
Cells(totrw – 5, 1).Select
Rows(totrw – 5).RowHeight = 12
‘======== KOLOM D DAN G DIEDIT ===========
Columns(“A:A”).ColumnWidth = 4.14
Columns(“B:B”).ColumnWidth = 4.86
Columns(“C:C”).ColumnWidth = 16.29
Columns(“D:D”).ColumnWidth = 21.29
Columns(“E:E”).ColumnWidth = 24.14
Columns(“F:F”).ColumnWidth = 15.86
Columns(“G:G”).ColumnWidth = 5.57
Columns(“H:H”).ColumnWidth = 17.29
Columns(“I:I”).ColumnWidth = 9.14
Columns(“J:J”).ColumnWidth = 23.71

‘========= PAGE SETUP =================
Range(Cells(1, 1), Cells(totrw, 10)).Select
With ActiveSheet.PageSetup
.PrintTitleRows = “”
.PrintTitleColumns = “”
End With
‘ActiveSheet.PageSetup.PrintArea = “”
ActiveSheet.PageSetup.PrintArea = Selection.Address
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
‘.PrintQuality = 180
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 70
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = “”
.EvenPage.CenterHeader.Text = “”
.EvenPage.RightHeader.Text = “”
.EvenPage.LeftFooter.Text = “”
.EvenPage.CenterFooter.Text = “”
.EvenPage.RightFooter.Text = “”
.FirstPage.LeftHeader.Text = “”
.FirstPage.CenterHeader.Text = “”
.FirstPage.RightHeader.Text = “”
.FirstPage.LeftFooter.Text = “”
.FirstPage.CenterFooter.Text = “”
.FirstPage.RightFooter.Text = “”
End With
‘=== SAVE AS DIHIDE DULU =======
ChDir “C:\Temp\INV”
ActiveWorkbook.SaveAs Filename:=”C:\Temp\INV\” & “INV-” & sname & “.xlsx”, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.WindowState = xlMaximized

Cells(1, 1).Select

finish:
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s