I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do

Purfleet · #1 10-07-2020, 10:42 PM

i know why you merge cells, but they will only cause problems - it is far better to 'center accross selection'

Code:

Sub SumCols()

Dim LR As Integer 'change to Long if your rows can go over 32000

LR = Cells(Rows.Count, 1).End(xlUp).Row ' Last row calculated on the first column

Range("d" & LR + 1) = "=Sum(d2:d" & LR & ")"
Range("f" & LR + 1) = "=Sum(f2:f" & LR & ")"
Range("g" & LR + 1) = "=Sum(g2:g" & LR & ")"


End Sub

1 - This will sum the 3 columns (you sure you want to sum the price?)
2 - The last row is based on column 1, so will always sum to the last row unless you put something like Total in row 13 then the previous total would be included in the next sum
3 - Can you clarify this part?

leeqiang · #2 10-08-2020, 08:58 PM

Quote:

Originally Posted by Purfleet

i know why you merge cells, but they will only cause problems - it is far better to 'center accross selection'

Code:

Sub SumCols()

Dim LR As Integer 'change to Long if your rows can go over 32000

LR = Cells(Rows.Count, 1).End(xlUp).Row ' Last row calculated on the first column

Range("d" & LR + 1) = "=Sum(d2:d" & LR & ")"
Range("f" & LR + 1) = "=Sum(f2:f" & LR & ")"
Range("g" & LR + 1) = "=Sum(g2:g" & LR & ")"


End Sub

1 - This will sum the 3 columns (you sure you want to sum the price?)
2 - The last row is based on column 1, so will always sum to the last row unless you put something like Total in row 13 then the previous total would be included in the next sum
3 - Can you clarify this part?

thx very much,the result is very good!

1.and sum the freight,the date of last columns.
2.can add a cells name is "total",automating add at the same row of sum ,such as column A of the sum row
3.clarify

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I copy a worksheet and paste values to another new workbook ?	DBenz	Excel	1	01-26-2019 07:03 AM
Sharing a Workbook that contains array formula link to other workbook	Ahmad.rage22@gmail.com	Excel	0	03-12-2018 10:15 PM
Outlook 2016 calendar monthly display	Herbert	Outlook	0	09-22-2017 04:41 AM
How do I populate Worksheets using information entirely from another Worksheet in the same Workbook?	BrieDanielle	Excel	1	06-18-2016 10:50 AM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook	JaxV	Excel	9	11-14-2014 12:25 AM