Incorrect CurrentRegion.SpecialCells(xlVisible).Rows.Count on filt

[KenY]

Hi
Got a problem that is driving me daft. Have looked at past threads and got
some help, but nothing to fix what I am seeing now.

I have a piece of code that is filtering a data list for a particular value
(in variable "tallyfilter"). When the data I am looking for starts on row 2
of the data table (row 1 has headers), I get the correct row count. However,
a specific instance that has me beaten at the moment is a dataset where the
target value starts on row 135. The row count is returned as '1' (=no data
visible) when in fact the data is actually filtered and visible when I look a
the spreadsheet in debug mode.

Is there anything wrong with my code? Looking at other threads I cannot
spot any errors and I am beginning to think there may be some unusual
character in my datalist. I have left in one suggested fix which was to use
the 'no data returned' error as my condition - unfortunately, it made no
difference as no error was reported (it saw data, but the rows.count didn't)!

fyi, I added a statement to select the whole data region before filtering;
when I did this and bypassed the condition rcntr=1, I did get the filtered
data to copy (but that gave me another problem, so I need to return the
correct row count).

Code is:

....
If tallyfilter = "ALL" Then
Else: Rows("1:1").Select
Selection.AutoFilter
Range("A1").Select
Selection.CurrentRegion.Select
Selection.AutoFilter Field:=2, Criteria1:=tallyfilter

End If

' count the number of rows in filtered data and exit if only the header is
present
rcntr = Selection.CurrentRegion.SpecialCells(xlVisible).Rows.Count
If rcntr = 1 Then Exit Sub
On Error GoTo nothing_to_copy
Selection.CurrentRegion.SpecialCells(xlVisible).Copy
On Error GoTo 0

....

Would be grateful if someone can help me out on this.

Thanks

 

[Dave Peterson]

This line:

Selection.CurrentRegion.SpecialCells(xlVisible).Rows.Count

Will give the number of rows in the first area of the that current region.

Here's a sample that I've used:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim VisRng As Range

Set wks = ActiveSheet

With wks
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
VisRng.EntireRow.Delete
End If
End With
End With
End Sub

It deletes the visible range, so you may want to change that part <vbg>.

 

[KenY]

Dave

Thanks for the explanation of why currentregion was the wrong choice for
applying .rows.count to.

I have used your method to make my procedure work - it also helped me to
simplify some of code around it.

Many thanks