|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to optimize the data summation code for each row and column in a data rectangle area?
How to optimize the data summation code for each row and column in a data rectangle area?
20201004134211.png The red area is the data. How to summarize the data of each column and each row in the 15th row and the kth column? Book1.xlsm How can the following code be optimized?thx Sub sumtheall() Dim i, j Columns("k:k").ClearContents Rows("15:15").ClearContents For i = 1 To 10 For j = 1 To 14 Cells(15, i) = Cells(15, i) + Cells(j, i) Next j Next i i = 1 j = 1 Do While Cells(j, i) <> "" For i = 1 To 10 Cells(j, 11) = Cells(j, 11) + Cells(j, i) Next i j = j + 1 i = 1 Loop End Sub |
#2
|
|||
|
|||
As long as the rows and columns are static, you just need to do a sum formula in the range without the fixed references
Sub sumtheall() Columns("k:k").ClearContents Rows("15:15").ClearContents Range("a15:j15") = "=sum(a1:a14)" Range("k1:k14") = "=Sum(a1:j1)" End Sub Last edited by Purfleet; 10-04-2020 at 02:36 AM. Reason: typo |
#3
|
|||
|
|||
Quote:
thanks for your help! I have some questions about this code: 1.Range("a15:j15") = "=sum(a1:a14)" I don't understand the meaning of the code,what's the use of this method of sum? 2.This code does not calculate the sum of all the total data in row 15 and put it in cell K15. How to solve it? |
#4
|
|||
|
|||
Run it and you will see that in a15 to j15 each column is now a sum of 1:14 as we have not put the $ the ranges are not locked so the columns are relative
The same for the rows if you want to sum the corner you can just do range(k15) = Sum(a1:j14) |
#5
|
|||
|
|||
Quote:
HTML Code:
Sub sumthe()Columns("k:k").ClearContentsRows("15:15").ClearContentsRange("a15:j15") = "=sum(a1:a14)"Range("k1:k14") = "=Sum(a1:j1)"Range("k15") = "=Sum(a1:j14)"End Sub thx |
#6
|
|||
|
|||
Quote:
Sub sumthe() Columns("k:k").ClearContents Rows("15:15").ClearContents Range("a15:j15") = "=sum(a1:a14)" Range("k1:k14") = "=Sum(a1:j1)" Range("k15") = "=Sum(a1:j14)" End Sub thx |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to copy specific ranges column data as row data from one worksheet to another using excel VBA | ganesang | Excel Programming | 1 | 03-26-2020 06:18 AM |
with different area of blocks, can we arrange automatically in a big rectangle area, Optimally | oneeyeevil | Excel Programming | 0 | 06-21-2019 03:45 AM |
Colored Rectangle in Column | eseel | Word Tables | 4 | 07-26-2017 06:07 AM |
How to convert multiple xml data in xl column to xl data | vivekanand.p | Excel Programming | 0 | 01-28-2016 08:13 AM |
How to edit cell data without changing print area. FIXED print area | VictoriaT8 | Excel | 3 | 02-22-2013 07:54 PM |