Sum values in a filtered sheet

[jgo1986]

Hello,
The following code applies a filter in a sheet and it does it right. Then, I apply a FOR and an IF to do some operations. The problem is that the for doesn't take into account the filter and it goes though the whole column instead. Do you know how can I fix this? Thanks!!

ultima_fila = Sheets(celdas).Cells(Rows.Count, "A").End(xlUp).Row
    
    ActiveSheet.Range("$A$1:$AM$" & ultima_fila).AutoFilter Field:=11, Criteria1:="1"
    ActiveSheet.Range("$A$1:$AM$" & ultima_fila).AutoFilter Field:=4, Criteria1:=provincia


    For i = 2 To ultima_fila
        suma_nodo_sampled = 0
        suma_nodo_unavail = 0
        For j = 2 To ultima_fila_inventario - 1
            If Range("S" & i).Value = Workbooks(inv_name).Sheets("Nodos").Range("A" & j).Value Then
                suma_nodo_sampled = suma_nodo_sampled + Range("j" & i).Value
                suma_nodo_unavail = suma_nodo_unavail + Range("i" & i).Value
            End If
        Next j
    Next i

 

[PatOBrien198]

Try:

suma_nodo_sampled = WorksheetFunction.Subtotal(9, Range("J2:J" & ultima_fila))
suma_nodo_unavail = WorksheetFunction.Subtotal(9, Range("I2:I" & ultima_fila))

 

[jgo1986]

Try:

suma_nodo_sampled = WorksheetFunction.Subtotal(9, Range("J2:J" & ultima_fila))
suma_nodo_unavail = WorksheetFunction.Subtotal(9, Range("I2:I" & ultima_fila))

Hello!

1st of all, thanks a lot for your help.

Perhaps I didn't explain myself well. The issue is in the for loop. There are 2 files:
1- In the first file there are like 100K rows, so i have to apply 2 filters
2- the second file 5K rows and there is no filter


Once I apply the filters, the first loop ( counter= i ) should only take into account the filtered rows, but the macro ignores the filter and the loop goes from i=1 to i=ultima_fila=100.000

What I need is to know how can I avoid all the rows which are no filtered.


thanks!!!

 

[MARK858]

You don't need to loop through the cells to sum them. Subtotal does that but it should be 109 and not 9 to sum only the visible cells.

suma_nodo_sampled = WorksheetFunction.Subtotal([COLOR="#FF0000"]109[/COLOR], Range("J2:J" & ultima_fila))
suma_nodo_unavail = WorksheetFunction.Subtotal([COLOR="#FF0000"]109[/COLOR], Range("I2:I" & ultima_fila))

Test the above and report back any issues.

 

[jgo1986]

You don't need to loop through the cells to sum them. Subtotal does that but it should be 109 and not 9 to sum only the visible cells.

suma_nodo_sampled = WorksheetFunction.Subtotal([COLOR=#ff0000]109[/COLOR], Range("J2:J" & ultima_fila))
suma_nodo_unavail = WorksheetFunction.Subtotal([COLOR=#ff0000]109[/COLOR], Range("I2:I" & ultima_fila))

Test the above and report back any issues.

Hello!
I think this method is not valid
Let me explain, I am comparing 2 files, so I have to loop at least the file where i have to leave the sum. So there is an IF conditional

If Range("S" & i).Value = Workbooks(inv_name).Sheets("Nodos").Range("A" & j).Value Then

there is a condition to sum. A data in a column of both files must exist.


regards

 

[MARK858]

What about (untested)...

suma_nodo_sampled = suma_nodo_sampled + Range("J2:J" &  & ultima_fila).SpecialCells(xlCellTypeVisible).Areas(i).Cells(1, 1).Value

Forget the above as it won't work if you have contiguous filtered cells

 

[MARK858]

Ugly way but maybe...

Set VisibleRange = Range("J2:J" & ultima_fila).SpecialCells(xlCellTypeVisible)
    If Range("S" & i).Value = Workbooks(inv_name).Sheets("Nodos").Range("A" & j).Value Then
        x = 1
        For Each xCell In VisibleRange
            If x = i - 1 Then
                suma_nodo_sampled = suma_nodo_sampled + xCell.Value
                Exit For
            End If
            x = x + 1
        Next xCell

Although I am not sure that I am understanding the relationship between i and the nth visible cell.