CODEWITHSUNDEEP
vbaexcelrangeautofilter
Sedako
Sedako

Reputation: 17

Excel VBA - Autofiltered row count is always returning 1

I'm currently using autofilter to filter on two columns. If the result of the autofilter is blank for only visible cells, it will add a new row. If any rows other than the header are found, it will instead show a MsgBox. The problem is that the row count is always returning 1. I've tried redefining "rng" a few ways to no avail. 

Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("Scored Items")

Worksheets("Scored Items").Activate

ws.AutoFilterMode = False

With ws

    .Range("A:D").AutoFilter Field:=1, Criteria1:=AssetBox.Text
    .Range("A:D").AutoFilter Field:=4, Criteria1:=PartBox.Text

    Set rng = .Range("A:A").SpecialCells(xlCellTypeVisible)

    If (rng.Rows.Count = 1) Then
        'Add new row based on VBA form
    Else
        MsgBox "Item has already been scored"
    End If
End With

ws.Cells.AutoFilter

Upvotes: 0

Views: 2118

Answers (2)

David Zemens
David Zemens

Reputation: 53623

If the range object is non-contiguous, than .Rows.Count will only return the number of rows in the first Area of the range, which in this case will be the "header" row. (NB: If your filter was such that the first row of data was visible, but the second was not, you'd get 2 as the result).

When working with filtered ranges, you need to iterate the Areas in the range.

    Dim aRange as Range
    For Each aRange in rng.Areas
        If (aRange.Rows.Count = 1) Then
            'Add new row based on VBA form
        Else
            MsgBox "Item has already been scored"
        End If
    Next

In this case, you probably want to flag some error if Areas.Count > 2 and also if any of the aRange.Rows.Count <> 1 etc.

If you're using AutoFilter merely to check for the existence of a value in this range (i.e., to prevent duplicate entries in a table?), that's a rather clunky way to do it and you would probably be better served using the COUNTIF function.

If Application.WorksheetFunction.CountIf(.Range("A:A"),AssetBox.Text) = 1 And _
    Application.WorksheetFunction.CountIf(.Range("D:D"), PartBox.Text) = 1 Then
    'Add new row based on VBA Form
Else
    MsgBox "Item has already been scored"
End If

Follow-up from your comment, since this is a UI, I would use both. Obviously, you want the AutoFilter to display the data to the user, so keep that. But rather than trying to hack around the various Areas of the filtered data, just use the COUNTIF function to check

'Filter data to display to the user
Dim dataRange As Range
Set dataRange = ws.Range("A:D")
With dataRange
    .AutoFilter Field:=1, Criteria1:=AssetBox.Text
    .AutoFilter Field:=4, Criteria1:=PartBox.Text

    'Check if part already been scored
    With Application.WorksheetFunction
    If .CountIf(.Columns(1), AssetBox.Text) = 1 And _
        .CountIf(.Columns(4), PartBox.Text) = 1 Then
        'Add new row based on VBA Form
    Else
        MsgBox "Item has already been scored"
    End If
End With
'unfilter the data
ws.Cells.AutoFilter

Upvotes: 4

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Instead of checking the Rows.Count, check Cells.Count for visible rows. Try it like this...

Dim ws As Worksheet
Dim rng As Range
Dim lr As Long
Set ws = Sheets("Scored Items")
lr = ws.UsedRange.Rows.Count

Worksheets("Scored Items").Activate

ws.AutoFilterMode = False

With ws
    .Range("A1:D" & lr).AutoFilter Field:=1, Criteria1:=AssetBox.Text
    .Range("A1:D" & lr).AutoFilter Field:=4, Criteria1:=PartBox.Text
    Set rng = .Range("A1:A" & lr).SpecialCells(xlCellTypeVisible)

    If rng.Cells.Count = 1 Then
        'Add new row based on VBA form
    Else
        MsgBox "Item has already been scored"
    End If
End With
ws.AutoFilterMode = False

Upvotes: 3

Related Questions