Hi Catalin,
Catalin wrote <snip:
I need something with VBA because the people who are using this sheet are
not familiar with such formulas and further more the content is
changing daily (with some VBA code).
In that case, set a column immediately to the left of your autofilter range
to receive your visible row numbers. In your code (which changes the
autofilter range content), add a final line like:
VisRowsNumber ActiveWorkbook, Sheets("MyFilteredSheet")
Which calls the following routine:
Sub VisRowsNumber(Optional WB As Workbook, _
Optional Sh As Worksheet)
Dim Arr()
Dim MyFilterRange As String
Dim rng As Range
Dim iFlt As Long, iCol As Long, k As Long
If WB Is Nothing Then Set WB = ActiveWorkbook
If Sh Is Nothing Then Set Sh = ActiveSheet
If Not Sh.AutoFilterMode Then Exit Function
With Sh.AutoFilter
MyFilterRange = .Range.Address
With .Filters
ReDim Arr(1 To .Count, 1 To 3)
For iFlt = 1 To .Count
With .Item(iFlt)
If .On Then
Arr(iFlt, 1) = .Criteria1
If .Operator Then
Arr(iFlt, 2) = .Operator
Arr(iFlt, 3) = .Criteria2
End If
End If
End With
Next
End With
Set rng = .Range.Columns(1).Offset(1, -1). _
Resize(.Range.Rows.Count - 1)
End With
If Sh.FilterMode Then ActiveSheet.ShowAllData
k = Sh.AutoFilter.Range.Row + 1
rng.Formula = "=SUBTOTAL(3,R" & k & "C[1]:RC[1])"
For iCol = 1 To UBound(Arr(), 1)
If Not IsEmpty(Arr(iCol, 1)) Then
If Arr(iCol, 2) Then
ActiveSheet.Range(MyFilterRange) _
.AutoFilter field:=iCol, _
Criteria1:=Arr(iCol, 1), _
Operator:=Arr(iCol, 2), _
Criteria2:=Arr(iCol, 3)
Else
ActiveSheet.Range(MyFilterRange). _
AutoFilter field:=iCol, _
Criteria1:=Arr(iCol, 1)
End If
End If
Next
End Sub
---
Regards,
Norman
"cata_and " wrote in message
...
Thank you Norman,
It works ! But I need something with VBA because the people who are
using this sheet are not familiar with such formulas and further more
the content is changing daily (with some VBA code).
Can somebody help me ?
Thanks and regards,
Catalin
Norman Jones wrote:
*Hi Catalin,
If your purpose is to assign a sequential number to each filtered
row. for a
non VBA solution, try:
Add a first column (say column A) to your data range
In cell A2 inset the formula =SUBTOTAL(3,$B$2:$B2)
Drag the formula down to the last data row
Filter your data
---
Regards,
Norman
"cata_and " wrote in
message
...
Thanks Norman,
Yes, I can reference the cells as you said, but I need to know how
many
rows are visible and I cannot do it with
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible).rows.count
Do you know other method ?
Regards,
Catalin
Norman Jones wrote:
*Hi Catalin,
Your filtered range could be referenced thus:
ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTy peVisible)
---
Regards,
Norman.
"cata_and " wrote in
message
...
Hi everybody,
Please help me with the following problem:
I have a range with abt 850 rows and 17 columns. I need to
hide
some of
them based on a criteria (which I can do it with VBA or
autofilter)
and
then I need to number (1,2,3 ....) the visible rows only (to
create
a
list and print it)
I create a VBA procedure which do this by looping through one
column of
the range, checking which cells are visible and assigning a
number
to
another column, in order, only to the visible rows.
The problem is that this procedure takes too long and the
computer
behaves like I have at least 65000 rows full of data. Imagine
that
there are only 850 rows. I heard that can be done much faster
by
assigning the range to an array.
Is there anyone who can give me a tip?
Thanks in advance
Catalin
PS
I have a pentium 4 with 256 MB RAM and Excel 2002.
---
Message posted from http://www.ExcelForum.com/
*
---
Message posted from http://www.ExcelForum.com/
*
---
Message posted from http://www.ExcelForum.com/