Counting Only Visible Rows

**Dom2012** · 11-22-2006, 12:55 PM

Hi,

I am stuck trying to find a solution to the following problem. What is clear is that it should not be written in VBA because I need the result to appear in every cell in column AW.

To summarise;

I have two macros that hide or unhide rows. Now I need a way to count only the non-hidden rows. The total needs to be visible in each visible cell of a single column (presently column AW).

Example - if rows 2 and 3 are hidden using the "Hide Row" macro, the following would happen;

Row 1 is visible, so it is numbered "1" in AW1.
Row 2 is not visible, so it either has no number or freezes at "1" in AW2.
Row 3 is not visible, so it also has no number or freezes at "1" in cell AW3.
Row 4 is visible, so it is numbered "2" in AW4.
And so on.

It doesn't really matter what is in the AW cell in hidden rows because I am not totalling rows. What I am doing is using it to define a print area. If a page has to be 70 rows long, another macro uses column AW to work out where to put page breaks (after row 70, 140, 210 and so on). If twenty rows were hidden on page 1, without the above solution, the printed page would only be 50 rows long. A solution to the above problem will always make sure only visible rows are counted when calculating where to put page breaks.

I am using Excel 2000.

I would be very grateful for any help because I'm really stuck on this one.

Thanks,

Dom

**Arien** · 11-22-2006, 01:02 PM

Hi Dom,

If you use the subtotal formula this may work for you.
Unlike the count formula it makes a difference between hidden and non-hidden lines.

Good luck.

Ariën

**Dom2012** · 11-22-2006, 01:05 PM

Hi,

Thanks for the suggestion. I tried the Subtotal formula but it did not work. This could easily be due to my lack of understanding of how this formula works.

Could you possibly suggest how I would use it?

All I know is that it would be in every cell in column AW.

Many thanks,

Dom

**Carim** · 11-22-2006, 01:08 PM

Hi,

It depends on your XL version ...
subtotal() features have been extended in XL2003 ...

HTH
Carim

**Carim** · 11-22-2006, 01:17 PM

A tiny VBA example to test your rows ...
adjust ranges to your needs ...

Sub Testrows()
    Dim myRng As Range
    Dim totalRows As Long
    Dim hiddenRows As Long
    Dim visibleRows As Long

    Range("A13:A22").EntireRow.Hidden = True
    Set myRng = Range("A1:A20")
    totalRows = myRng.Cells.Count
    visibleRows = myRng.Cells.SpecialCells(xlCellTypeVisible).Cells.Count
    hiddenRows = totalRows - visibleRows
    MsgBox totalRows & vbLf & visibleRows & vbLf & hiddenRows
End Sub

HTH
Carim

**Arien** · 11-22-2006, 01:19 PM

Hi,

You could use something like
=SUBTOTAL(3;$A$1:INDIRECT("A"&ROW()-1))+1

Every line with a value in column A will be counted this way.
I have to admit that this forumula only works with a filter, not with
a total hidden line. So it may not work to your needs

(I am using Excel 2000)

regards,
Arien

Counting Only Visible Rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Counting Only Visible Rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions