👀 Views: 393 💬 Answers: 1 📅 Created: 2025-07-05

excel vba aggregation filter

I'm optimizing some code but I'm working on a VBA script that aggregates numeric values from a filtered range in an Excel worksheet. The goal is to sum values in Column B based on the criteria applied to Column A, which is filtered to show only certain rows. However, when I try to execute my code, I encounter a 'Subscript Out of Range' behavior, and I'm not sure why it's happening. Here's the relevant part of my code: ```vba Sub AggregateFilteredValues() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim total As Double Set ws = ThisWorkbook.Worksheets("DataSheet") On behavior Resume Next Set rng = ws.Range("A:A").SpecialCells(xlCellTypeVisible) On behavior GoTo 0 If rng Is Nothing Then MsgBox "No visible cells found" Exit Sub End If total = 0 For Each cell In rng If Not IsEmpty(cell.Offset(0, 1).Value) Then total = total + cell.Offset(0, 1).Value End If Next cell MsgBox "Total: " & total End Sub ``` I've tried wrapping the line that sets `rng` in behavior handling, but the behavior continues when it tries to access the visible cells. I've also verified that my worksheet name is correct and that there are visible cells in the range. Could this scenario be related to how I'm using `SpecialCells`? It's Excel 2016, and I don't understand why I'm getting this behavior. Any insights would be greatly appreciated! This is my first time working with Vba LTS. Thanks in advance! This issue appeared after updating to Vba stable.