SOLVED

Sum of rows with the help of VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2507710%22%20slang%3D%22en-US%22%3ESum%20of%20rows%20with%20the%20help%20of%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2507710%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EI%20want%20to%20create%20a%20total%20column%20at%20the%20end%20that%20sums%20each%20row.%3C%2FP%3E%3CP%3Eso%20instead%20of%20summing%20each%20column%20for%20Jan%2C%20Feb%20and%20Mar%20vertically%2C%20I%20want%20to%20sum%20each%20row%20for%20Jan%2C%20Feb%2C%20and%20Mar%20horizontally%26nbsp%3B%20in%20each%20sheet%20with%20the%20help%20of%20VBA%20code%3C%2FP%3E%3CP%3Elike%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(4100).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292954i7CFE5DADCFF34F6D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(4100).png%22%20alt%3D%22Screenshot%20(4100).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%26nbsp%3B%20a%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2507710%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2508121%22%20slang%3D%22de-DE%22%3ESubject%3A%20Sum%20of%20rows%20with%20the%20help%20of%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508121%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20ways%20that%20lead%20to%20Rome....%20again%20as%20many%20possibilities%20in%20Excel%20to%20approach%20a%20problem%2C%20let%20alone%20the%20vast%20number%20of%20suggested%20solutions%20that%20arise%20from%20it.%3CBR%20%2F%3ESimplified%3A%20Everything%20is%20possible%20with%20Excel%20...%20if%20not%20everything%2C%20then%20most%20of%20it%20%3A)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20suggested%20solution%20with%20a%20formula%2C%20don't%20need%20always%20VBA%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ftranspose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ETRANSPOSE%20function%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESometimes%20you%20need%20to%20switch%20or%20rotate%20cells.%20You%20can%20do%20this%20by%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ftranspose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ecopying%2C%20pasting%2C%20and%20using%20the%20Transpose%20option%3C%2FA%3E%3CSPAN%3E.%20But%20doing%20that%20creates%20duplicated%20data.%20If%20you%20don't%20want%20that%2C%20you%20can%20type%20a%20formula%20instead%20using%20the%20TRANSPOSE%20function.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2508257%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20rows%20with%20the%20help%20of%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508257%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20reply.%3C%2FP%3E%3CP%3EBut%20sir%20i%20want%20sum%20of%20each%20category%20with%20the%20help%20of%20VBA..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2508444%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20rows%20with%20the%20help%20of%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CreateRowSums()%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20lr%20As%20Long%0A%20%20%20%20Dim%20lc%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20For%20Each%20wsh%20In%20Worksheets%0A%20%20%20%20%20%20%20%20lr%20%3D%20wsh.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20%20%20%20%20lc%20%3D%20wsh.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByColumns%2C%20SearchDirection%3A%3DxlPrevious).Column%0A%20%20%20%20%20%20%20%20wsh.Cells(2%2C%20lc).Resize(lr%20-%201).FormulaR1C1%20%3D%20%22%3DSUM(RC3%3ARC%5B-1%5D)%22%0A%20%20%20%20Next%20wsh%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

 I want to create a total column at the end that sums each row.

so instead of summing each column for Jan, Feb and Mar vertically, I want to sum each row for Jan, Feb, and Mar horizontally  in each sheet with the help of VBA code

like - 

Screenshot (4100).png

 

Please help..??

 

Here is  a attached file...

6 Replies

@Zan_Hanifee 

There are many ways that lead to Rome .... again as many possibilities in Excel to approach a problem, let alone the vast number of suggested solutions that arise from it.
Simplified: Everything is possible with Excel ... if not everything, then most of it :)).

 

Here is a suggested solution with a formula, dont need always VBA

TRANSPOSE function

Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function.

 

 

Hope I could help

 

Nikolino

I know I don't know anything (Socrates)

Thank you for the reply.

But sir i want sum of each category with the help of VBA..

 

Please help..??

best response confirmed by allyreckerman (Microsoft)
Solution

@Zan_Hanifee 

Sub CreateRowSums()
    Dim wsh As Worksheet
    Dim lr As Long
    Dim lc As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        lr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        wsh.Cells(2, lc).Resize(lr - 1).FormulaR1C1 = "=SUM(RC3:RC[-1])"
    Next wsh
    Application.ScreenUpdating = True
End Sub
Thank you So Much sir.
It works

@Zan_Hanifee 

Forgive me for asking, but why are you attracted to VBA when ordinary worksheet formulas should provide the same results?

@Peter Bartholomew 

Hello Sir,

Thank you for the response

This formula is Great

Actually i practice Excel formula as well as VBA code.