Help with totaling a columns in VBA
I have some code that I have built. (may not be completely effecient yet)
However, I have a spread sheet that the data starts in cell H9 that I want to maneuver. The purpose of the code is to create a row to populate a total for each column. I used this forum to grab the code: (Cells(Rows.Count, "h").End(xlUp)(2).FormulaR1C1 = "=Sum(R10C:R[-1]C)") however, this places the total at the bottom of the column. How would I state where I want the total to be placed? Secondly. The spread sheet comes with totals on the bottom that are not in a usable format. I would like to be able to incorporate a code that finds that row of data and deletes it. My sample code is below: Here is a same of table data: Fcst 200521 200522 1 1,713.99 1,629.17 2 2,047.80 1,946.41 Total Blank 1,100.61 1,046.07 <= This row to be deleted. Row number would not be constant. Rows("9:9").Select Selection.Insert Shift:=xlDown Range("D9").Select ActiveCell.FormulaR1C1 = "Total" Cells(Rows.Count, "h").End(xlUp)(2).FormulaR1C1 = "=Sum(R10C:R[-1]C)" Range("H9").Select Selection.Copy Range("I9:BH9").Select Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("H9").Select Selection.End(xlDown).Select Range("I36:BI36").Select Application.CutCopyMode = False Selection.ClearContents Range("A1").Select Thanks for any help. End Sub |
Help with totaling a columns in VBA
Nevermind this post. I used a different method.
"Chris" wrote: I have some code that I have built. (may not be completely effecient yet) However, I have a spread sheet that the data starts in cell H9 that I want to maneuver. The purpose of the code is to create a row to populate a total for each column. I used this forum to grab the code: (Cells(Rows.Count, "h").End(xlUp)(2).FormulaR1C1 = "=Sum(R10C:R[-1]C)") however, this places the total at the bottom of the column. How would I state where I want the total to be placed? Secondly. The spread sheet comes with totals on the bottom that are not in a usable format. I would like to be able to incorporate a code that finds that row of data and deletes it. My sample code is below: Here is a same of table data: Fcst 200521 200522 1 1,713.99 1,629.17 2 2,047.80 1,946.41 Total Blank 1,100.61 1,046.07 <= This row to be deleted. Row number would not be constant. Rows("9:9").Select Selection.Insert Shift:=xlDown Range("D9").Select ActiveCell.FormulaR1C1 = "Total" Cells(Rows.Count, "h").End(xlUp)(2).FormulaR1C1 = "=Sum(R10C:R[-1]C)" Range("H9").Select Selection.Copy Range("I9:BH9").Select Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("H9").Select Selection.End(xlDown).Select Range("I36:BI36").Select Application.CutCopyMode = False Selection.ClearContents Range("A1").Select Thanks for any help. End Sub |
All times are GMT +1. The time now is 10:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com