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/