Memasukkan Nilai yang Berbeda-beda dalam satu Kolom

10 Responses to Memasukkan Nilai yang Berbeda-beda dalam satu Kolom

1. 

   qq

   March 25, 2009 at 4:45 am

   gimana sc buat grafik yg gabung antara line dg bar

   Reply
   • 

     aurino

     March 27, 2009 at 3:48 am

     Buat grafik biasa garis dulu misalnya lalu klik salah satu titik dalam grafik yang akan diubah dan ubah jenis grafiknya (chart type)
     contohnya adalah sbb: download disini

     Reply
   • 

     aurino

     March 27, 2009 at 4:29 am

     Sudah dijawabkan

     Reply
2. 

   Kakang

   June 26, 2010 at 1:19 pm

   Kalo source datanya dari sheet lain gimana bos??

   Reply
   • 

     aurino

     July 3, 2010 at 12:03 am

     Maksudnya ??? data yang mana?

     Reply
3. 

   yuda

   October 17, 2011 at 7:14 am

   kok tidak bisa tak coba. keluar peringatan kalo sintaxnya kurang benar

   Reply
4. 

   sutrisno

   December 22, 2011 at 9:57 pm

   gan, gimana caranya membuat makronya, agar kita bisa mencetak halaman tertentu dan mencetak selurunya. mks

   Reply
   • 

     aurino

     December 23, 2011 at 7:28 pm

     Print tips for Excel
     Ron de Bruin (last update 31-Aug-2007)
     Go back to the Excel tips page

     1. Printing non-contiguous areas on one page
     2. Hide rows, columns or cells when you print a sheet
     3. Print odd and even pages
     4. Print visible, Hidden or all worksheets
     5. Do not print Header or Footer on all pages
     6. Print the Last Save time in the Footer of every sheet you print
     7. Printing a Workbook’s Full Path in a Header or Footer (John WalkenBach)
     8. Print every Worksheet with a value In cell A1
     9. Print the same worksheet ? time with the number in cell, Header or Footer
     10. Print Formulas
     11. Insert Page Breaks every ? rows
     12. One liners to Print a workbook, sheet, sheets, selection…..

     Printing non-contiguous areas on one page

     Insert a new worksheet (you’re going to use this for printing).
     Do this for each area you want to print on the same page.
     (The areas don’t have to be on the same sheet)

     If you change the original range, the picture will change too (values and formatting!).
     You can use this sheet always to print because it will always update if you change
     The original data.

     Excel 97-2003

     Select the range
     Edit | Copy in the menu bar (or use Ctrl-C)
     Go to the new worksheet and with the Shift key pressed click on Edit | Paste Picture Link.
     Go back and do the same for the other areas.

     Note: You see that the edit menu will change when you press the shift button when you click on Edit

     Excel 2007

     Select the range
     Home tab | Clipboard group | Copy (or use Ctrl-C)
     Go to the new worksheet and Click on the arrow on the Paste button in the Clipboard group on
     the Home tab and choose As Picture >Paste Picture Link.
     Go back and do the same for the other areas.

     Print selection or range with one or more areas with a macro.

     The macro will add a new sheet and copy all the selection areas on it.
     Then it will print and delete the sheet.

     Sub Test()
     Dim Destrange As Range
     Dim Smallrng As Range
     Dim Newsh As Worksheet
     Dim Ash As Worksheet
     Dim Lr As Long

     Application.ScreenUpdating = False

     Set Ash = ActiveSheet
     Set Newsh = Worksheets.Add
     Ash.Select

     Lr = 1

     ‘You can also use a range with more areas like this
     ‘For Each smallrng In Ash.Range(“A1:C1,D10:G20,A30″).Areas

     For Each Smallrng In Selection.Areas
     Smallrng.Copy
     Set Destrange = Newsh.Cells(Lr, 1)
     Destrange.PasteSpecial xlPasteValues
     Destrange.PasteSpecial xlPasteFormats
     Lr = Lr + Smallrng.Rows.Count
     Next Smallrng

     Newsh.Columns.AutoFit

     Newsh.PrintOut

     Application.DisplayAlerts = False
     Newsh.Delete
     Application.DisplayAlerts = True

     Application.ScreenUpdating = True

     End Sub

     Hide rows, columns or cells when you print a sheet

     AutoFilter or Advanced Filter is also a very good way to print only the things you want
     in the sheet, filter the range with your criteria and print the sheet.
     Check out this site for examples http://www.contextures.com/tiptech.html

     But it is not always possible to get the result with a filter, See the examples below for
     another way to hide Rows/Cells.

     If you use one of the Print options in Excel the event below will check the ActiveSheet name
     and run the code. This example will run if the ActiveSheet name = “Sheet1”

     1) It will hide row 10:15
     2) Print
     3) Unhide row 10:15

     Copy/Paste this event in the Thisworkbook module

     Where do I paste the code that I want to use in my workbook
     http://www.rondebruin.nl/code.htm

     Private Sub Workbook_BeforePrint(Cancel As Boolean)
     If ActiveSheet.Name = “Sheet1″ Then
     Cancel = True
     Application.EnableEvents = False
     Application.ScreenUpdating = False
     With ActiveSheet
     .Rows(“10:15″).EntireRow.Hidden = True
     .PrintOut
     .Rows(“10:15″).EntireRow.Hidden = False
     End With
     Application.EnableEvents = True
     Application.ScreenUpdating = True
     End If
     End Sub

     Or hide columns (this example hide column B and D)

     With ActiveSheet
     .Range(“B1,D1″).EntireColumn.Hidden = True
     .PrintOut
     .Range(“B1,D1″).EntireColumn.Hidden = False
     End With

     Or hide all rows with a blank cell in column A

     With ActiveSheet
     On Error Resume Next
     .Columns(“A”).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
     .PrintOut
     .Columns(“A”).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
     On Error GoTo 0
     End With

     Make the Font white of a range

     You can replace the red lines in the BeforePrint event above
     with the example code below if you want to use it.
     Or you can use the examples below in a macro.

     1) It will make the font white
     2) Print
     3) Make the font black again

     ‘Range with one area
     With ActiveSheet
     .Range(“B10:B14″).Font.ColorIndex = 2
     .PrintOut
     .Range(“B10:B14″).Font.ColorIndex = 1
     End With

     ‘Range with more areas
     With ActiveSheet
     .Range(“A1:A3,B10:B14,C12″).Font.ColorIndex = 2
     .PrintOut
     .Range(“A1:A3,B10:B14,C12″).Font.ColorIndex = 1
     End With

     ‘All cells with a error
     With ActiveSheet
     .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 2
     .PrintOut
     .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 1
     End With

     Use conditional formatting

     Make the Font white with conditional formatting
     http://www.contextures.com/xlCondFormat03.html#Print

     If you only want to print unprotected cells then use Dave Peterson’s example below

     Pick out a cell that you can use for a flag (say X1–but you can use any cell
     you want, but keep it out of the print range).

     Then select your range to hide/print.

     Excel 97-2003: Format|Conditional formatting
     Excel 2007: “Conditional formatting” In the Styles group on the Home tab
     Click on New Rule and choose “Use a formula to determine which cells to format”

     The formula is:
     =AND($X$1=”hide”,CELL(“Protect”,A1)=1)
     make it white font on white fill (or blue on blue or …)
     Note: A1 in the formula is the first cell in your selection

     When you want to print, enter hide in X1 to hide, print and clear X1 when you’re ready.

     Hide Empty rows, Print and unhide the rows

     This example will loop through every row in the range
     Set rng = Sheets(“Sheet1″).Range(“A1:A30″)

     If every cell in column A:G is empty it will hide that row.
     After the loop it print the sheet and then unhide the rows.

     Change “A1:G1″ in the macro to the cells you want.
     You can also use this with non contiguous ranges like “B1,D1:G1″

     Sub Hide_Print_Unhide()
     Dim rw As Long
     Dim rng As Range
     Dim cell As Range

     Application.ScreenUpdating = False

     Set rng = Sheets(“Sheet1″).Range(“A1:A30″)

     With rng.Columns(1)
     For Each cell In rng
     If Application.WorksheetFunction.CountA( _
     .Parent.Cells(cell.Row, 1).Range(“A1:G1″)) = 0 Then _
     .Parent.Rows(cell.Row).Hidden = True
     Next cell
     .Parent.PrintOut
     .EntireRow.Hidden = False
     End With

     Application.ScreenUpdating = True
     End Sub

     Print odd and even pages
     This option is not available in Excel but you can use a macro to do it.

     Sub Print_Odd_Even()
     Dim Totalpages As Long
     Dim StartPage As Long
     Dim Page As Integer

     StartPage = 1 ’1 = Odd and 2 = Even

     ‘Or use the InputBox suggestion from Gord Dibben
     ‘StartPage = InputBox(“Enter 1 for Odd, 2 for Even”)

     Totalpages = Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)
     For Page = StartPage To Totalpages Step 2
     ActiveSheet.PrintOut from:=Page, To:=Page, _
     Copies:=1, Collate:=True
     Next
     End Sub

     Print visible, Hidden or all worksheets

     If you want to print a whole workbook you can use this code line

     ThisWorkbook.PrintOut Or ActiveWorkbook.PrintOut

     But this will not print hidden Worksheets.
     You can use this macro to print hidden and visible Worksheets

     Sub Print_Hidden_And_Visible_Worksheets()
     ‘Dave Peterson
     Dim CurVis As Long
     Dim sh As Worksheet
     For Each sh In ActiveWorkbook.Worksheets
     With sh
     CurVis = .Visible
     .Visible = xlSheetVisible
     .PrintOut
     .Visible = CurVis
     End With
     Next sh
     End Sub

     To print only hidden sheets use

     With Sh
     CurVis = .Visible
     If CurVis >= 0 Then
     .Visible = xlSheetVisible
     .PrintOut
     .Visible = CurVis
     End If
     End With

     Do not print Header or Footer on all pages

     The example below will only print the right header on the first page of the ActiveSheet.

     You have these options
     (LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, RightFooter)

     Check out the VBA help for all formatting codes.
     Look for “Formatting Codes for Headers and Footers”

     You can use something like this :

     .CenterFooter = “&8Page &P & of &N”
     .RightFooter = “&8Last Saved : &B” & ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)
     .LeftFooter = “&8″ & ActiveWorkbook.FullName & Chr(10) & “Sheetname : &B” & ActiveSheet.Name

     Sub Test()
     Dim TotPages As Long
     TotPages = Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)
     With ActiveSheet.PageSetup
     .RightHeader = “Your Header info”
     ActiveSheet.PrintOut From:=1, To:=1
     .RightHeader = “”
     ActiveSheet.PrintOut From:=2, To:=TotPages
     End With
     End Sub

     Tip: If you not want to print the last page you can do this
     ActiveSheet.PrintOut From:=2, To:=TotPages -1

     You can also make a different header for the last page if you want

     Print the Last Save time in the Footer of every sheet you print

     If you copy this in the ThisWorkbook module it will print the Last Save Time
     in the Right Footer of every sheet when you use one of the Print options in Excel.
     Note: The Property is not working correct in Excel 97.

     Where do I paste the code that I want to use in my workbook
     http://www.rondebruin.nl/code.htm

     Private Sub Workbook_BeforePrint(Cancel As Boolean)
     Dim wkSht As Worksheet
     For Each wkSht In ThisWorkbook.Worksheets
     wkSht.PageSetup.RightFooter = “&8Last Saved : ” & _
     Format(ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”), _
     “yyyy-mmm-dd hh:mm:ss”)
     Next wkSht
     End Sub

     If you use Excel 97 you can use this to add the Date in a worksheet cell

     Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
     Cancel As Boolean)
     ‘If you save the file the date/time will be placed in cell A1 of Sheet1
     Sheets(“Sheet1″).Range(“A1″).Value = Format(Now, “yyyy-mmm-dd hh:mm:ss”)
     End Sub

     Print every Worksheet with a value In cell A1

     With this macro you loop through every worksheet and if there Is a
     value in a certain cell it will add the sheet to the array and print it.
     You can also test a for a word like Sh.Range(“A1″).Value = “PrintMe”

     Sub Print_All_Worksheets_With_Value_In_A1()
     Dim Sh As Worksheet
     Dim Arr() As String
     Dim N As Integer
     N = 0
     For Each Sh In ActiveWorkbook.Worksheets
     If Sh.Visible = xlSheetVisible And Sh.Range(“A1″).Value “” Then
     N = N + 1
     ReDim Preserve Arr(1 To N)
     Arr(N) = Sh.Name
     End If
     Next
     With ActiveWorkbook
     .Worksheets(Arr).PrintOut
     End With
     End Sub

     Print the same worksheet ? time with the number in cell, Header or Footer

     This example will print ? copies of the same sheet (It use a Input box to ask you how many)
     It will copy the page number in cell A1 or in the Header or Footer.

     Note: you can use this for testing .PrintOut preview:=True

     Sub PrintCopies_ActiveSheet_1()
     Dim CopiesCount As Long
     Dim CopieNumber As Long
     CopiesCount = Application.InputBox(“How many copies do you want”, Type:=1)

     For CopieNumber = 1 To CopiesCount
     With ActiveSheet
     ‘ This example print the number in cell A1
     .Range(“A1″).Value = CopieNumber & ” of ” & CopiesCount

     ‘If you want the number in the footer use this line
     ‘.PageSetup.LeftFooter = CopieNumber & ” of ” & CopiesCount

     ‘Print the sheet
     .PrintOut
     End With
     Next CopieNumber
     End Sub

     The example below continue printing where It left off, such as today you print
     numbered pages 1-25 and the next time when you enter 10 in the input box it print 26-35.

     Sub PrintCopies_ActiveSheet_2()
     ‘ This example print the number in cell A1
     Dim CopiesCount As Long
     Dim CopieNumber As Long
     CopiesCount = Application.InputBox(“How many copies do you want”, Type:=1)

     With ActiveSheet
     If Not IsNumeric(.Range(“A1″).Value) Then .Range(“A1″).Value = 0

     For CopieNumber = 1 To CopiesCount
     .Range(“A1″).Value = .Range(“A1″).Value + 1

     ‘Print the sheet
     .PrintOut

     Next CopieNumber
     End With
     End Sub

     Print Formulas

     If you want to print your formulas then you can toggle the view with this

     Excel 97-2003: Tools – Options – View and check Formulas
     Excel 2007: “Show Formulas” in the Formula Auditing group on the Formula tab

     Or the shortcut CTRL ` in all Excel versions

     Check out also this example from John Walkenbach.
     http://j-walk.com/ss/excel/tips/tip37.htm

     And David McRitchie’s site
     http://www.mvps.org/dmcritchie/excel/formula.htm

     Insert Page Breaks every ? rows

     If row 1 is a header row and you want to print it on every page then
     change RW + 1 to RW + 2 and use File>Page Setup>Sheet to fill in $1:$1
     in the “Rows to repeat at top: ” box.

     This example will add breaks every 20 rows from row 1 till the last row with data in column A.

     Sub Insert_PageBreaks()
     Dim Lastrow As Long
     Dim Row_Index As Long
     Dim RW As Long

     ‘How many rows do you want between each page break
     RW = 20

     With ActiveSheet
     ‘Remove all PageBreaks
     .ResetAllPageBreaks

     ‘Search for the last row with data in Column A
     Lastrow = .Cells(Rows.Count, “A”).End(xlUp).Row

     For Row_Index = RW + 1 To Lastrow Step RW
     .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
     Next
     End With
     End Sub

     One liners to Print a workbook, sheet, sheets, selection…..

     Look in the VBA help for PrintOut and see that you can use the following arguments.
     expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

     Note: Remember that you can’t print sheets that are hidden
     If you print the whole workbook with the first example there is no problem but the example
     for Sheets or worksheets (example 2 and 3) will not work if there are hidden sheets.

     ActiveWorkbook.PrintOut
     ‘the whole workbook

     Worksheets.PrintOut
     ‘all worksheets

     Sheets.PrintOut
     ‘all sheets

     Sheets(Array(“Sheet1″, “Sheet3″)).PrintOut
     ‘all sheets in the array

     ActiveWindow.SelectedSheets.PrintOut
     ‘print all selected sheets

     ActiveSheet.PrintOut
     ‘only the activesheet

     Sheets(“Sheet1″).PrintOut
     ‘only “Sheet1″

     Selection.PrintOut
     ‘print only the selection

     Range(“C1:D5″).PrintOut
     ‘print range

     Reply
5. 

   Adhink

   March 28, 2013 at 4:59 am

   *maaf.., gimana macrox gan, data tidak boleh dua kali input….

   Reply
   • 

     aurino

     March 30, 2013 at 12:46 am

     @Adhink: lihat

     Reply