| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help Cleaning up Code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help Cleaning up Code
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help Cleaning up Code
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help Cleaning up Code
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help Cleaning up Code
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cleaning Product Code list | Excel Worksheet Functions | |||
| Cleaning up some VB | Excel Programming | |||
| Help with shortening/cleaning some code please | Excel Programming | |||
| Cleaning up data | Excel Programming | |||
| VBA Code -- Cleaning Data | Excel Programming | |||
Linear Mode
