[Solved] how to hide rows and the total summary also change

[bigme]

Dear friends,
kindly help me, i have a table with some number in it and i sum for total.
problem came up when i hide a few rows but the total doesn't change according to the showing number.
is there any code to solve my problem? thank you.

regards,
bigMe

 

[shamimarita]

Dear friends,
kindly help me, i have a table with some number in it and i sum for total.
problem came up when i hide a few rows but the total doesn't change according to the showing number.
is there any code to solve my problem? thank you.

regards,
bigMeView attachment 61

Dear Bigme,

Thanks for posting your query.

To sum total with hidden rows/numbers you have to use the AGGREGATE function.

The formula of your problem is : =AGGREGATE(9,1,A2:A8)

Here, I'm attaching some images to describe the method.

First, select and write the AGGREGATE function, Next type 9 for the SUM function.



Then, type 1. Here, 1 means it will ignore hidden rows while summing selected values.




Here, is your final output.



Regards
Thanks

 

[bigme]

dear Ms/Mrs Shami
thank you for your explanation, could you kindly help me for the vba code to hide rows based on condition and do the aggregate, thank you

regards,
bigMe

 

[Yousuf Shovon]

Hello Bigme,
Glad we could help with our suggestion to use the AGGREGATE function to sum the total with hidden rows/numbers in the previous reply. I understand you wish to hide rows based on a condition through VBA and perform the in-built AGGREGATE function.

Assuming the condition is: Hide rows when value > 5, we will calculate the total using the VBA code. Let's see the following illustrations for a better understanding.

Now, write the following VBA code and Run it.

Sub HideRowsAndCalculateTotal()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long
    Dim j As Long
    Dim total As Double
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("B5:D" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            If rng.Cells(i, j).Value > 5 Then
                rng.Rows(i).EntireRow.Hidden = True
                Exit For
            Else
                rng.Rows(i).EntireRow.Hidden = False
            End If
        Next j
    Next i
    For j = 1 To rng.Columns.Count
        total = Application.WorksheetFunction.Aggregate(9, 3, rng.Columns(j).SpecialCells(xlCellTypeVisible))
        rng.Cells(rng.Rows.Count + 1, j).Value = total
    Next j
End Sub

This code uses a nested loop to check each cell in the range for values greater than 5. If any cell in a row has a value greater than 5, the entire row is hidden and the loop moves on to the next row. If none of the cells in a row have values greater than 5, the row is unhidden and returns the totals of the unhidden data thoroughly.

Thus we obtain the desired total based on the condition.


Attached is the Excel file for you to practice. Hope this helps. You can always reach us for similar problems here.

Best Regards,
Yousuf Shovon

 

[bigme]

Dear Mr. Yousuf,
thank you very much your code is very useful.

regards,
bigMe

 

[Yousuf Shovon]

Hello bigMe,

Glad to hear the code was useful. If you have any further questions or need more assistance, feel free to ask. The community is always here to help and support each other.

Best Regards.