Re: Incorrect CurrentRegion.SpecialCells(xlVisible).Rows.Count on
- From: KenY <KenY@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 6 Feb 2009 08:45:01 -0800
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
--
KenY
"Dave Peterson" wrote:
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 wrote:
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
--
KenY
--
Dave Peterson
- References:
- Prev by Date: Re: arrays and labeling
- Next by Date: Fighting a date format
- Previous by thread: Re: Incorrect CurrentRegion.SpecialCells(xlVisible).Rows.Count on filt
- Next by thread: Error 1004, clean memory?
- Index(es):
Relevant Pages
|
