How to optimize the data summation code for each row and column in a data rectangle area?

leeqiang · #6 10-04-2020, 06:53 PM

Quote:

Originally Posted by Purfleet

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

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