Need help Cleaning up Code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joel Mills (millsj_at_bayltd.com)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 11:43:37 -0600

The Following Code cleans up data imported from a scheduling program. I
have used the recorder and snippets of code I've found to create the
following. I'm sure there is a more efficient way to write this code. I
have begun cleaning it up but I'm sure there must be an alternate way to
delete the columns that allows for the fact that the columns are shifted
over. For example because "Col's B & D" are deleted "Col. E" becomes "Col
C" when it began as "Col E".

Any help would be appreciated.
Joel

Sub Cleanup_Convert()
'This Begins the Cleanup Process
Worksheets("Export").Activate
    Range("1:2").Delete
    Range("B:b").Delete
    Range("D:D").Delete
    Range("C:C").Delete
    Range("G:H").Delete
    Range("A1").Value = ("Discipline")
    Range("B1").Value = ("Week Beginning")
    Range("C1").Value = ("Early Ave.")
    Range("D1").Value = ("Early Cumm.")
    Range("E1").Value = ("Late Ave.")
    Range("F1").Value = ("Late Cumm.")
'This Adds the Planned Column
    Range("H1") = "Planned Ave. Manpower"
    Range("H2").FormulaR1C1 = "=AVERAGE(RC[-4],RC[-2])"
    Range("H2").AutoFill Destination:=Range("H2:H18")
'This Converts the Planned Manpower by dividing by the number of Days Worked
    Rows("1:1").Insert Shift:=xlDown
    Range("H1").FormulaR1C1 = "5"
    Range("H1").Copy
    Range("H3:H19").PasteSpecial Paste:=xlAll
    Selection.NumberFormat = "0.0"
'This Adds Week Ending Column
    Range("G2").Value = "Week Ending"
    Range("G3").Formula = "=B3+6"
    Range("G3").AutoFill Destination:=Range("G3:G19")
    Columns("G:G").Columns.AutoFit
'This Adds the Column for Early % based on Cummulative Early Values
    Range("D1").Formula = "=MAXA(d3:d7000)"
    Range("I3").Formula = "=d3/d$1"
    Range("I3").AutoFill Destination:=Range("I3:I19")
    Range("I2").Value = ("Target Early %")
'This Adds the Column for Late % and calculates based on Cummulative Late
Values
'a row is also inserted between column G this allows the CurrentRegion to
work
'correctly
    Range("F1").Formula = "=MAXA(f3:f7000)"
    Range("j3").Formula = "=F3/F$1"
    Range("J3").AutoFill Destination:=Range("J3:J19")
    Range("j2").Value = ("Target Late %")
    Range("I:J").Select
        Selection.NumberFormat = "0.0%"
    'Wraps and Centers Titles on Row 2
    Rows("2:2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Worksheets("Charts").Activate
End Sub

Relevant Pages

  • Re: Coding to choose the previous completed cell in a column
    ... Sub gotocolacell() ... I have used the Macro recorder to create a macro for me however when I apply ... go up to the previously filled cell in column A and complete a set function. ... Selection.Borders.LineStyle = xlNone ...
    (microsoft.public.excel.programming)
  • Re: Meaning of some VBA
    ... When you record a macro, the recorder copie all actions, some of which may ... Sub AutoShape2_Click ... ' AutoShape2_Click Macro ... Private Function UpdateSheet ...
    (microsoft.public.excel.programming)
  • RE: Pivot Table Macro
    ... this is what came out of the recorder after selecting the top two names ... Sub Filter_Rep ... SkipBlanks _ ... and we can help clean it up. ...
    (microsoft.public.excel.programming)
  • Re: Making code less column-specific
    ... I just recorded this to show you how the recorder can be your friend. ... Sub Macro5() ... Dim row1value As String ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Remove sound from Action Buttons with VBA
    ... from the recorder.) ... Steve Rindsberg wrote: ... On each slide, I need to remove that sound, ... > Sub Macro1() ...
    (microsoft.public.powerpoint)