Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Cells when using Filters
I would like to count only the visible cells in order to set the
number of pages when using filters on a database. Has anyone reasoned out the correct coding? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Cells when using Filters
Since you posted in the Excel Programming area, I think this may not really
be what you want, but you didn't give a whole lot of information, or requirements, so this may actually work for you. http://www.techonthenet.com/excel/formulas/subtotal.php Regards, Ryan-- -- RyGuy "Canlink" wrote: I would like to count only the visible cells in order to set the number of pages when using filters on a database. Has anyone reasoned out the correct coding? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Cells when using Filters
you can try something like this. in this example, my column headers are in row
5, columns A thru U on sheet1. Option Explicit Sub test() Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row Dim filtlastrow As Long With ws.Range("A5:U" & lastrow) .AutoFilter field:=1, Criteria1:="01" filtlastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row If .Columns(1).SpecialCells(xlCellTypeVisible).Cells. Count = 1 Then 'nothing but headers are visible Else MsgBox ws.Range("A6:A" & _ filtlastrow).SpecialCells(xlCellTypeVisible).Count End If End With End Sub -- Gary "Canlink" wrote in message ... I would like to count only the visible cells in order to set the number of pages when using filters on a database. Has anyone reasoned out the correct coding? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Cells when using Filters
On Jun 12, 12:00 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
you can try something like this. in this example, my column headers are in row 5, columns A thru U on sheet1. Option Explicit Sub test() Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row Dim filtlastrow As Long With ws.Range("A5:U" & lastrow) .AutoFilter field:=1, Criteria1:="01" filtlastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row If .Columns(1).SpecialCells(xlCellTypeVisible).Cells. Count= 1 Then 'nothing but headers arevisible Else MsgBox ws.Range("A6:A" & _ filtlastrow).SpecialCells(xlCellTypeVisible).Count End If End With End Sub -- Gary "Canlink" wrote in message ... I would like tocountonly thevisiblecellsin order to set the number of pages whenusingfilterson a database. Has anyone reasoned out the correct coding? Gary: The coding is almost what I am looking for and I think I can modify your suggestion to count the visible rows in a filtered database so I can set the number of pages that will be printing showing this selection. Thanks Geoff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of visible, non-blank cells only? | Excel Discussion (Misc queries) | |||
looking up for subtotals stretched criteria to count visible cells | Excel Discussion (Misc queries) | |||
Sum of visible cells with multiple filters - Office 2007 | Excel Worksheet Functions | |||
Count only visible cells | Excel Discussion (Misc queries) | |||
count only visible cells | Excel Worksheet Functions |