Saturday 18 August 2012

VB Program - Formatting a Excel Report -Various Formatting Techniques

VB Program - Formatting a excel report -Various Formatting Techniques.

Program :

The below code has various paramters that can be used to format the excel repot(column width,wrap text,printing page setup) etc.
Kindly refer to comments mentioned in the code to understand better.

Sub Formatting()

   ' i is the no. of rows in the sheet
   Dim i As Integer
    i = 57
 
   ' we can modify the width as below
   'formatting starts for column A

   Columns("A:A").ColumnWidth = 5.43

    ' formatting the rows through the loop

    Range("A2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     End With
    Selection.Offset(1, 0).Select
   Loop

  
    Columns("B:B").ColumnWidth = 8.14
    Range("B2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

      
       Columns("C:C").ColumnWidth = 9.29
       Range("C2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
          End With
    Selection.Offset(1, 0).Select
   Loop
   
     
   
    Columns("D:D").ColumnWidth = 37.71
   Range("D2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
       .Orientation = 0
       .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Offset(1, 0).Select
   Loop
  

      Columns("E:E").ColumnWidth = 46.86
     Range("E2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
      .Orientation = 0
       .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Offset(1, 0).Select
   Loop
  

   Columns("F:F").ColumnWidth = 15
    Range("F2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  
  
  
   Columns("G:G").ColumnWidth = 11.29
    Range("G2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

     Columns("H:H").ColumnWidth = 29.71
     Range("H2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

  
   Columns("I:I").ColumnWidth = 10.43
    Range("I2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

    Range("J2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

   Columns("K:K").ColumnWidth = 7.57
    Range("K2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

     Range("L2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

  
  ' Changing the Page setup For priniting purposes

 
   With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
               
        .LeftMargin = Application.InchesToPoints(0.15748031496063)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.236220472440945)
        .BottomMargin = Application.InchesToPoints(0.15748031496063)
        .HeaderMargin = Application.InchesToPoints(0.15748031496063)
        .FooterMargin = Application.InchesToPoints(0.275590551181102)
        .Orientation = xlLandscape
        .Draft = False
        .Zoom = 68

    End With

    End Sub


Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

No comments:

Post a Comment