Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-03-2020, 11:11 PM
leeqiang leeqiang is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Advanced Beginner
How to optimize the data summation code for each row and column in a data rectangle area?
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 10-04-2020, 02:33 AM
Purfleet Purfleet is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 10-04-2020, 02:50 AM
leeqiang leeqiang is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Advanced Beginner
How to optimize the data summation code for each row and column in a data rectangle area?
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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
20201004174248.png


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?
Reply With Quote
  #4  
Old 10-04-2020, 03:08 AM
Purfleet Purfleet is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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)
Reply With Quote
  #5  
Old 10-04-2020, 06:52 PM
leeqiang leeqiang is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Advanced Beginner
How to optimize the data summation code for each row and column in a data rectangle area?
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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)





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
Reply With Quote
  #6  
Old 10-04-2020, 06:53 PM
leeqiang leeqiang is offline How to optimize the data summation code for each row and column in a data rectangle area? Windows 10 How to optimize the data summation code for each row and column in a data rectangle area? Office 2019
Advanced Beginner
How to optimize the data summation code for each row and column in a data rectangle area?
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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)



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
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to optimize the data summation code for each row and column in a data rectangle area? 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 optimize the data summation code for each row and column in a data rectangle area? How to edit cell data without changing print area. FIXED print area VictoriaT8 Excel 3 02-22-2013 07:54 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft