0

I'm filtering a spreadsheet that is providing results. For some reason, it's following the error protocol I have built-in rather than doing what is expected.

    Sheets("Sheet1").Range("B4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter Field:=14, Criteria1:="DED*", Operator:=xlOr, Criteria2:="DEX*"
    On Error GoTo Errline1

    Dim Count1 As Range
    Set Count1 = Range(Selection, Selection.SpecialCells(xlCellTypeLastCell))

    Dim Line As Range

    For Each Line In Count1.SpecialCells(xlCellTypeVisible).Areas
        Count1 = Count1 + Line.Rows.Count
    Next Line

I'm expecting this to set Count1 as the number of rows that are visible so I can then use that number as part of an if statement further down. However, once it gets to the "Next Line" portion, it errors and goes to Errline1

Miles Fett
  • 711
  • 4
  • 17
JTL27
  • 3
  • 3
  • 4
    count1 is a range object. can a number be added to a Range object? – Jeremy Sep 12 '19 at 18:43
  • 2
    Very important reading matter: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Sep 12 '19 at 18:59
  • 2
    Even if it made sense to add a number and a range object, your for-each loop would be mutating `Count1` while simultaneously iterating over it. More often than not, mutating the thing you are iterating over is a bug. – John Coleman Sep 12 '19 at 19:05
  • 2
    `Count1` has too many meanings, you're missing a variable somewhere. – Mathieu Guindon Sep 12 '19 at 19:12

0 Answers0