Need help Cleaning up Code

J

Joel Mills

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
J

Joel Mills

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Joel Mills

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel Mills

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 said:
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 said:
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 said:
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
Click to expand...
this
is
repeated when other columns are deleted. As I've said by trial and
Click to expand...
error
I
delete the correct columns containing data that isn't needed. Not sure if
Offset is the correct approach.


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
Click to expand...
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)


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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help, Macro wont run from a particular worksheet 3
AutoFill Formula with code 11
Sub runs, but gives error when called. 7
Macro to insert formula failing...need help 3
I need some changes in a code 5
Macro causes Excel to crash 3
Trouble selecting multiple sheets using RDB code 11
VBA code copying formula not running correctly 3

Top