About Us

ryguy7272

Take a look at this:
http://www.contextures.com/xlautofilter03.html

Regards,
Ryan---

--
RyGuy

"James" wrote:

Thanks for the reply. I ended up using:
rwcount = .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count -1
Worked great

"Dave Peterson" wrote:

rng.rows.count will give you the number of rows in the first area of rng (and my
bet is your rng consists of multiple non-contiguous areas).

rng.rows.count is equivalent to rng.areas(1).rows.count

If you want to get the number of rows in the autofilter range (and set a
variable for just the details), you can do something like this that I saved from
a previous post:

With activesheet.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).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

James wrote:

Hi, I have the following code, how come I get the value of 1 everytime i do
rng.rows.count (which is wrong)? Im using rng.rows.count in another private
function. Thanks in advance

Public Sub RangeFind()
Dim shtName As String
Dim wShData As Worksheet
shtName = ActiveSheet.Name
Set wShData = ActiveWorkbook.Worksheets(shtName)

With wShData.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1,
.Columns.Count).SpecialCells xlCellTypeVisible)
On Error GoTo 0
End With
End Sub

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
autofilter row count	Pierre[_18_]	Excel Programming	3	December 21st 07 03:03 PM
AutoFilter Row Count	[email protected]	Excel Programming	4	August 11th 07 01:10 PM
AutoFilter and row count	Bob	Excel Discussion (Misc queries)	5	March 29th 07 12:40 AM
Autofilter and Cell Count	asmenut	Excel Programming	1	November 3rd 05 08:23 PM
Autofilter record count	Debbie Thompson	Excel Discussion (Misc queries)	1	February 11th 05 05:32 PM

Menu

About Us