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

KJRowe · Jan 24, 2005

Hi all, just found this site, and all I can say from looking thru some of the threads so far is "WOW"

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

HalfAce · Jan 24, 2005

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.

Sub B291_CleanUp()
'
' B291_CleanUp Macro
' Macro recorded 1/24/2005 by Kevin Rowe
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Application.ScreenUpdating = False
Columns("A:D").Delete Shift:=xlToLeft
With Range("E4:H4")
 .Merge
 .Value = "Days in Inventory"
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
Columns("C:C").Delete Shift:=xlToLeft
With Range("M4")
 .Value = "Historical Run Rates"
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
Range("R5").Value = "Guelph"
Range("S5").Value = "Vancouver"
Range("R4").Value = "Warehouse Location"
With Range("R4:S4")
 .Merge
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
Cells.EntireColumn.AutoFit
Range("A2").Value = "EMJ Confidential Inventory Report for:"
Range("A2").Font.Bold = True
Rows("5:5").Font.Underline = xlUnderlineStyleSingle
With Range("A3")
 .Borders(xlDiagonalDown).LineStyle = xlNone
 .Borders(xlDiagonalUp).LineStyle = xlNone
 .Borders(xlEdgeLeft).LineStyle = xlNone
 .Borders(xlEdgeTop).LineStyle = xlNone
End With
With Range("A3").Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
End With
With Range("A3")
 .Borders(xlEdgeRight).LineStyle = xlNone
 .Font.Italic = True
 .Font.Bold = True
End With
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
Application.ScreenUpdating = True
End Sub

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

Hope it helps,
Dan

KJRowe · Jan 24, 2005

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 · Jan 24, 2005

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.

Sub B291_CleanUp()
'
' B291_CleanUp Macro
' Macro recorded 1/24/2005 by Kevin Rowe
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Application.ScreenUpdating = False
Columns("A:D").Delete Shift:=xlToLeft
With Range("E4:H4")
 .Merge
 .Value = "Days in Inventory"
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
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").End(xlUp)))
[G65536].End(xlUp)(2, 1) = G
H = WorksheetFunction.Sum(Range("H4", Range("H65536").End(xlUp)))
[H65536].End(xlUp)(2, 1) = H
Columns("C:C").Delete Shift:=xlToLeft
With Range("M4")
 .Value = "Historical Run Rates"
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
Range("R5").Value = "Guelph"
Range("S5").Value = "Vancouver"
Range("R4").Value = "Warehouse Location"
With Range("R4:S4")
 .Merge
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .ReadingOrder = xlContext
End With
Cells.EntireColumn.AutoFit
Range("A2").Value = "EMJ Confidential Inventory Report for:"
Range("A2").Font.Bold = True
Rows("5:5").Font.Underline = xlUnderlineStyleSingle
With Range("A3")
 .Borders(xlDiagonalDown).LineStyle = xlNone
 .Borders(xlDiagonalUp).LineStyle = xlNone
 .Borders(xlEdgeLeft).LineStyle = xlNone
 .Borders(xlEdgeTop).LineStyle = xlNone
End With
With Range("A3").Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
End With
With Range("A3")
 .Borders(xlEdgeRight).LineStyle = xlNone
 .Font.Italic = True
 .Font.Bold = True
End With
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
Application.ScreenUpdating = True
End Sub

Dan

KJRowe · Jan 24, 2005

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 · Jan 24, 2005

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.

Hope some of this helps,
Dan

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

KJRowe

New Member

Excel Facts

HalfAce

MrExcel MVP

KJRowe

New Member

HalfAce

MrExcel MVP

KJRowe

New Member

HalfAce

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

KJRowe

New Member

Excel Facts

HalfAce

MrExcel MVP

KJRowe

New Member

HalfAce

MrExcel MVP

KJRowe

New Member

HalfAce

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock