Stuck in a macro...how to total columns...

KJRowe

New Member
Joined
Jan 24, 2005
Messages
3
Hi all, just found this site, and all I can say from looking thru some of the threads so far is "WOW" :eek:

This site's going to be an invaluable source of informatino for me, and hopefully as I progress, I can help out too :)

On to my question...our system generates some pretty ugly inventory reports by vendor and I thought I'd make a simple formatting macro to run on them...it works like a dream, but I'm wondering now...I have 4 columns of aged inventory amounts, and would like to add to the macro some code to total those individual columns so I can see aging totals at a glance. The problem is...one vendors' report may have only 50-60 rows (SKUs) and another vendor could have hundreds. Is there a way to add to the macro to have the total appear in those columns after the last row, even though it's different for every report, or am I wishing too much? :)

If this can't be done easily, it's not a big issue, since I guess I've already cut 15-20 mins of work down to 3-4 seconds with the macro - wouldn't kill me to total 4 more columns manually LOL

Thanks, and I've attached my macro below in case it helps (if we're not supposed to attach our macro code, accept my apologies)?

Sub B291_CleanUp()
'
' B291_CleanUp Macro
' Macro recorded 1/24/2005 by Kevin Rowe
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Columns("A:D").Select
Selection.Delete Shift:=xlToLeft
Range("E4:H4").Select
ActiveCell.FormulaR1C1 = "Days in Inventory"
Range("E4:H4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Range("I6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("M4").Select
ActiveCell.FormulaR1C1 = "Historical Run Rates"
Range("M4:P4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("R5").Select
ActiveCell.FormulaR1C1 = "Guelph"
Range("S5").Select
ActiveCell.FormulaR1C1 = "Vancouver"
Range("R4").Select
ActiveCell.FormulaR1C1 = "Warehouse Location"
Range("R4:S4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.LargeScroll ToRight:=-1
Rows("4:5").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.FormulaR1C1 = "EMJ Confidential Inventory Report for:"
Range("A2").Select
Selection.Font.Bold = True
Rows("5:5").Select
Selection.Font.Underline = xlUnderlineStyleSingle
Range("A3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Font.Italic = True
Selection.Font.Bold = True
Range("A3").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "EMJ Confidential"
.RightHeader = ""
.LeftFooter = "Prepared by Kevin Rowe &D"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Kevin, welcome to the board.
Which rows exactly do you want to see totaled up?

I took the liberty to clean up some unnecessary stuff the macro recorder adds. This should speed up your routine a little bit. You might try running this in a copy of your workbook & see if I left anything out.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> B291_CleanUp()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' B291_CleanUp Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 1/24/2005 by Kevin Rowe</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Keyboard Shortcut: Ctrl+Shift+C</SPAN>
<SPAN style="color:#007F00">'</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Columns("A:D").Delete Shift:=xlToLeft
<SPAN style="color:#00007F">With</SPAN> Range("E4:H4")
    .Merge
    .Value = "Days in Inventory"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Columns("C:C").Delete Shift:=xlToLeft
<SPAN style="color:#00007F">With</SPAN> Range("M4")
    .Value = "Historical Run Rates"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("R5").Value = "Guelph"
Range("S5").Value = "Vancouver"
Range("R4").Value = "Warehouse Location"
<SPAN style="color:#00007F">With</SPAN> Range("R4:S4")
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Cells.EntireColumn.AutoFit
Range("A2").Value = "EMJ Confidential Inventory Report for:"
Range("A2").Font.Bold = <SPAN style="color:#00007F">True</SPAN>
Rows("5:5").Font.Underline = xlUnderlineStyleSingle
<SPAN style="color:#00007F">With</SPAN> Range("A3")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("A3").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("A3")
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Font.Italic = <SPAN style="color:#00007F">True</SPAN>
    .Font.Bold = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
ActiveSheet.PageSetup.PrintArea = ""
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "EMJ Confidential"
    .RightHeader = ""
    .LeftFooter = "Prepared by Kevin Rowe &D"
    .CenterFooter = ""
    .RightFooter = "Page &P of &N"
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = <SPAN style="color:#00007F">False</SPAN>
    .PrintGridlines = <SPAN style="color:#00007F">False</SPAN>
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = <SPAN style="color:#00007F">True</SPAN>
    .CenterVertically = <SPAN style="color:#00007F">False</SPAN>
    .Orientation = xlPortrait
    .Draft = <SPAN style="color:#00007F">False</SPAN>
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = <SPAN style="color:#00007F">False</SPAN>
    .Zoom = <SPAN style="color:#00007F">False</SPAN>
    .FitToPagesWide = 1
    .FitToPagesTall = <SPAN style="color:#00007F">False</SPAN>
    .PrintErrors = xlPrintErrorsDisplayed
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
ActiveWindow.SelectedSheets.PrintPreview
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


When I know for sure where you want those totals, we'll throw those in too.

Hope it helps,
Dan
 

KJRowe

New Member
Joined
Jan 24, 2005
Messages
3
Ummm, wow Dan...LOL thanks...I'm going to look over your changes so I keep an understanding of what the macro is doing (and what I had in there that was unnecessary).

thanks again :)

The columns I'm looking to total are:

With Range("E4:H4")
.Merge
.Value = "Days in Inventory"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Kevin,
Give this a shot and see if it does what you want.

The "extra" stuff that was removed just pertained to things like the scroll column statements and all the selections. You seldom have to actually select an object to work with it and it's quite a bit faster if you don't...
Anyway, see if this does it.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> B291_CleanUp()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' B291_CleanUp Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 1/24/2005 by Kevin Rowe</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Keyboard Shortcut: Ctrl+Shift+C</SPAN>
<SPAN style="color:#007F00">'</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Columns("A:D").Delete Shift:=xlToLeft
<SPAN style="color:#00007F">With</SPAN> Range("E4:H4")
    .Merge
    .Value = "Days in Inventory"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
E = WorksheetFunction.Sum(Range("E4", Range("E65536").End(xlUp)))
[E65536].End(xlUp)(2, 1) = E
F = WorksheetFunction.Sum(Range("F4", Range("F65536").End(xlUp)))
[F65536].End(xlUp)(2, 1) = F
G = WorksheetFunction.Sum(Range("G4", Range("G65536").<SPAN style="color:#00007F">End</SPAN>(xlUp)))
[G65536].<SPAN style="color:#00007F">End</SPAN>(xlUp)(2, 1) = G
H = WorksheetFunction.Sum(Range("H4", Range("H65536").<SPAN style="color:#00007F">End</SPAN>(xlUp)))
[H65536].<SPAN style="color:#00007F">End</SPAN>(xlUp)(2, 1) = H
Columns("C:C").Delete Shift:=xlToLeft
<SPAN style="color:#00007F">With</SPAN> Range("M4")
    .Value = "Historical Run Rates"
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Range("R5").Value = "Guelph"
Range("S5").Value = "Vancouver"
Range("R4").Value = "Warehouse Location"
<SPAN style="color:#00007F">With</SPAN> Range("R4:S4")
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Cells.EntireColumn.AutoFit
Range("A2").Value = "EMJ Confidential Inventory Report for:"
Range("A2").Font.Bold = <SPAN style="color:#00007F">True</SPAN>
Rows("5:5").Font.Underline = xlUnderlineStyleSingle
<SPAN style="color:#00007F">With</SPAN> Range("A3")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("A3").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("A3")
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Font.Italic = <SPAN style="color:#00007F">True</SPAN>
    .Font.Bold = <SPAN style="color:#00007F">True</SPAN>
End <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End <SPAN style="color:#00007F">With</SPAN>
ActiveSheet.PageSetup.PrintArea = ""
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "EMJ Confidential"
    .RightHeader = ""
    .LeftFooter = "Prepared by Kevin Rowe &D"
    .CenterFooter = ""
    .RightFooter = "Page &P of &N"
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = <SPAN style="color:#00007F">False</SPAN>
    .PrintGridlines = <SPAN style="color:#00007F">False</SPAN>
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = <SPAN style="color:#00007F">True</SPAN>
    .CenterVertically = <SPAN style="color:#00007F">False</SPAN>
    .Orientation = xlPortrait
    .Draft = <SPAN style="color:#00007F">False</SPAN>
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = <SPAN style="color:#00007F">False</SPAN>
    .Zoom = <SPAN style="color:#00007F">False</SPAN>
    .FitToPagesWide = 1
    .FitToPagesTall = <SPAN style="color:#00007F">False</SPAN>
    .PrintErrors = xlPrintErrorsDisplayed
End <SPAN style="color:#00007F">With</SPAN>
ActiveWindow.SelectedSheets.PrintPreview
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
End <SPAN style="color:#00007F">Sub</SPAN></FONT>

Dan
 

KJRowe

New Member
Joined
Jan 24, 2005
Messages
3
Well, it worked perfectly...thanks again!

I didn't expect anyone to do it for me...but this works great...I think I'll be able to decipher what those extra lines are doing no problem.

I asume the xlUp is what moves the calculation up to the line below the last row? That's where I was having brain meltdown :)

Dan, thanks again :)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Well, sort of...
The xlUp refers to one of two things. If you start in a blank cell, then it will look (going up) for the first cell it finds that isn't blank. Conversely, if you start in a used cell, it will look for the first blank. The statement:
.End(xlUp)(2, 1) refers to the cell below the cell it found. (This can be a little more intuitive if written like this:
.End(xlUp).Offset(1, 0) - (One row down and 0 columns to the right.) They do the same thing but I think End(xlUp)(2, 1) is a little more efficient.

You also have at your disposal,
.End(xlDown)
.End(xlToRight)
.End(xlToLeft)

Using these (with maybe some offsets), you can choose where you want to get to, without knowing ahead of time exactly where that'll be. :biggrin:

Hope some of this helps,
Dan
 

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top