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/

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/

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(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 i
message
...
Hi everybody,

Please help me with the following problem:

I have a range with abt 850 rows and 17 columns. I need to hid
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 creat
a
list and print it)

I create a VBA procedure which do this by looping through on
column of
the range, checking which cells are visible and assigning a numbe
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

Catalin,

Try

ActiveSheet.AutoFilter.Range.Columns(1).SpecialCel ls(xlCellTypeVi
sible).Count


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




"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/

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/

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/

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/