Thread: Need help Cleaning up Code
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Joel Mills Joel Mills is offline
external usenet poster
  
Posts: 79
Default Need help Cleaning up Code
Stan,

Thanks for the reply. That didn't occur to me. I'll keep this in mind in
the future. I was able to reduce 4 lines of code to one after using the
+Ctrl Select Columns while recording a macro and cleaning it up. I mention
this incase someone else might have the same situation or a similar one.
I'm still very new to VBA and looking for methods to write more efficient
code. My code now has been reduced to this:

Sub Cleanup_Convert()
'This Begins the Cleanup Process
Worksheets("Export").Activate
Range("1:2").Delete
Range("B:B,D:E,J:K").Delete Shift:=xlToLeft
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").Value = "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
With Rows("2:2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Worksheets("Charts").Activate
End Sub


"stanshoe" wrote in message
...
Joel-

When I am programtically adding and deleting columns in a worksheet I find
it much easier to start from the last column instead of Column A. If I
need
to delete colums B, D and F, I would delete F first. That way, I don't
have
to worry about the new position of the next column to delete. It is still
column D.

I hope this helps.

-Stan Shoemaker
Palo Alto, CA

"Joel Mills" wrote:

By Using the recorder and cleaning up the code I was able to delete
columns
B, D, E, J, & K with the following:

Sub Delete_All_Columns()
'
'This deletes the appropriate columns
Range("B:B,D:E,J:K").Delete Shift:=xlToLeft
End Sub



"Joel Mills" wrote in message
...
Thanks for the reply, but I'm not deleting columns B thru D. First
Col B
gets deleted. This causes the columns to the right to shift. I have
used
trial and error to change the next range that is deleted to result in
a
column with data that isn't necessary for my spreadsheet. Ultimately
when
the data is imported the following columns need to be deleted.
Columns B,
D, E, J, & K. I'm not sure how to accomplish this because when "B" is
deleted the remaining columns shift over one place to the left. And
this
is
repeated when other columns are deleted. As I've said by trial and
error
I
delete the correct columns containing data that isn't needed. Not
sure if
Offset is the correct approach.


"Bob Phillips" wrote in message
...
Just a little bit

'This Begins the Cleanup Process
Worksheets("Export").Activate
Range("1:2").Delete
Range("B:D").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").Value = "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("J2").Value = ("Target Late %")
Range("J3").AutoFill Destination:=Range("J3:J19")
Range("I:J").NumberFormat = "0.0%"
'Wraps and Centers Titles on Row 2
With Rows("2:2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Worksheets("Charts").Activate


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joel Mills" wrote in message
...
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











Reply With QuoteReply With Quote