Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel Summary VBA

Posted on 2014-12-07
Medium Priority
365 Views
Hello,
I'm trying to get the Grand Total at the end of my sheet , (Find last Row + 1) and Highlight.

Grand Total = Sum of the Totals of each city .

I have about 1500 - 2000 Rows.

Any Help is appreciated.
Sample.xlsx
0
Question by:W.E.B
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 52

Accepted Solution

Rgonzo1971 earned 600 total points
ID: 40486232
Hi,
pls try with subtotal

see example

Regards
Sample-V1.xlsx
0

LVL 33

Assisted Solution

Rob Henson earned 600 total points
ID: 40486570
Assuming your data doesn't start out with the rows for summing each city, you can add subtotals as rgonzo has suggested and then use the following to update them:

``````Sub Add_Subtotal()
Selection.CurrentRegion.Select
Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(5, 6, 7, 8 _
, 9, 10), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
``````
Thanks
Rob H
0

LVL 16

Expert Comment

ID: 40486581
In the OUTLINE Group under the DATA menu, select the Subtotals icon to add one or multiple subtotals to your Excel list of data.   The data must have a header row and must be an Excel list (cannot be formatted as an Excel table).   With the cursor located in the data range, go to the Outline Group located under the DATA menu and select Subtotals.  The dialog box shown below will display and allow you to select the columns to sum ( or count, avg, min, max, etc…) and the column to use for the "On Change" column (City in this case).

Once the Subtotals are displayed, to the left of the row numbers are expand and collapse buttons.  Pressing one of these will expand or collapse the list of data to show only the city rows, etc…   Adding or deleting rows (and new cities, etc…)  will automatically update the subtotals.

You can highlight the Subtotal and the Grand Total Rows and they will maintain the color highlight as data is added or deleted.

Another option for this type of reporting is to use a Pivot Table.   The attached file has samples of both the Subtotals and the pivot tables.

HTH,
JerryQ-28576016.xlsx
0

LVL 16

Assisted Solution

Jerry Paladino earned 800 total points
ID: 40486838
I reread your question and if you want the VBA to find the last row in the worksheet you can try this code...
``````Sub LastRow_WS()
' Returns the last used row in the active worksheet
Dim rLastCell As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Debug.Print "The last used row in this Worksheet is: " & rLastCell.Row
End Sub
``````
0

Author Closing Comment

ID: 40487549
thank you all.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …