Excel VBA - hidden rows / autofilter

C

cata_and

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.
 
N

Norman Jones

Hi Catalin,

Your filtered range could be referenced thus:

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
 
C

cata_and

Thanks Norman,

Yes, I can reference the cells as you said, but I need to know how man
rows are visible and I cannot do it with

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).rows.count

Do you know other method ?

Regards,
Catalin
 
C

Chip Pearson

Catalin,

Try

ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVi
sible).Count


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Norman Jones

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
 
C

cata_and

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 said:
*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


message
Click to expand...
 
N

Norman Jones

Hi Catalin,



Catalin wrote <snip>:


I need something with VBA because the people who are using this sheet are
Click to expand...
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 > said:
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 said:
*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


message
Click to expand...
Click to expand...
 
Top