How many rows?
in [Programming]
|
Prev: Excel 2007 used with Oracle ADI
Next: rename message
From: John on 4 Aug 2007 23:40 In article <0E69B8F1-9DA8-4190-A37A-0E682AAF47A8(a)microsoft.com>, Tom Ogilvy <TomOgilvy(a)discussions.microsoft.com> wrote: > Hrllo John > > Look at help for Subtotal in Excel itself for all options. > I didn't say the object browser or VBA help. Subtotal is a worksheet > function being used in VBA. And note this is the Worksheet Function > Subtotal, not the method Subtotal which is found under the Data menu. > > Subtotal(3,Range) counts numbers in the range, so the column would need to > contain numbers. Subtotal(3,range) counts non-empty cells, so the column > would need to not be empty. If you want to get an accurate count. > > Excel has a group and outline capability which can be found under the Data > menu in Excel itself. I don't know if that adds anything to what you are > trying to do. > > Note that all my examples in the previous post were performed on a single > column. In this way, rng.count gives the number of visible rows. It also > started in A2 so that the header row was excluded in the count. > > > s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:="" > produces an Autofilter which displays only the rows that have blanks in > column SrtCol, so subtotal applied to that column would return nothing - see > the previous discussion > > from the immediate window: > \ > SrtCol = 2 > set s = Activesheet > s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:="" > Set Rng = s.UsedRange.SpecialCells(xlCellTypeVisible) > ? Rng.count > 42 > > so I got a count, but it doesn't mean much because it is all visible cells > in the usedrange. > > ? intersect(rng,columns(1)).Count > 7 > > so looking at a single column tells me 7 rows are visible (including the > header row). > > -- > Regards, > Tom Ogilvy Tom, First of all, no the group and outline functions in Excel will NOT do what I need. I guess I must have a different picture of what "visible" means. When my filter is applied to a file with 67 rows, it produces a selected set of 6 rows. Using the Set Rng = s.usedrange.specialcells(xlCellTypeVisible) should then give a row count of 6 since those are the only "visible" rows in the used range. Obviously my concept of "visible" is wrong. Oh well. Let me get back to my original question. Yes I did ask how to get a count of rows in a filter but I also asked how to loop through only the rows found by the filter. The specific thing I need is to activate the first row in the filtered set (not the filter header row), so that I can insert my group label row ahead of it. John > > > > "John" wrote: > > > In article <4FB30EC2-4891-4B6F-BBA8-F38E74AD2554(a)microsoft.com>, > > Tom Ogilvy <TomOgilvy(a)discussions.microsoft.com> wrote: > > > > > use the subtotal worksheet function > > > > > > if application.Subtotal(3,Range("A2:A500")) > > > > > > > > > where Column A holds numbers. Look at help for Subtotal in Excel itself > > > for > > > all options. > > > > > > Subtotal only counts the visible cells in the filtered range. > > > > > > Dim rng as Range > > > On Error Resume Next > > > set rng = Range("A2:A500").SpecialCells(xlVisible) > > > On Error goto 0 > > > if not rng is nothing then > > > for each cell in rng > > > > > > -- > > > Regards, > > > Tom Ogilvy > > > > Tom, > > Thanks for the quick reply. The process for doing what I want is > > obviously less elegant than doing the same thing in Project. I guess > > that is because Excel always has 256 columns and 65K rows whether there > > is data in them or not. In Project the task collection object, for > > example, is only as big as the number of tasks entered by the user. > > > > I took a look at the Subtotal worksheet function description in the > > object browser. That has got to be one of the least comprehensible > > descriptions I've read. At the Worksheet Object level, there is > > absolutely no explanation of what the arguments are for and the example > > only covers the Range Object level, which does have a decent description > > of the arguments. I should complain, I have found many places in the > > Project object browser descriptions that are either flat out wrong, > > misleading or confusing. > > > > The SpecialCells Method for the Range object looks more promising. I > > doubt I would have found that obscure method during my own search of > > Excel's object model. Basically what I am doing is replicating the > > grouping function available in MS Project. The overall macro I'm writing > > exports selected data from Project to Excel. One subroutine finds rows > > with columns containing certain data. It then adds a row above that > > group for a group label. > > > > Unfortunately either I'm not understanding something or the SpecialCells > > Method doesn't work as advertised. My worksheet consists of 67 rows by 9 > > columns (i.e. the UsedRange). The following code snippet is part of the > > above mentioned subroutine (s is my worksheet object and SrtCol is a > > variable defining a particular column). In this case, I'm looking for no > > data in the SrtCol. In other cases I'm looking for non-blank data so I > > use the Find Method after the autofilter and that works fine. > > > > s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:="" > > On Error Resume Next > > Set Rng = s.UsedRange.SpecialCells(xlCellTypeVisible) > > > > I stepped through the code. The autofilter finds 6 rows that meet the > > criteria. That works fine. However when I use the Count property on the > > rows of the Rng object, it only counts one row. That's not right, or at > > least, it is not what I need. I need it to set the Rng object as the 6 > > found rows. > > > > Your further insight would be appreciated. > > > > John > > > > > > > > > > > > "John" wrote: > > > > > > > People, > > > > I have an Excel worksheet and I have applied an autofilter to a > > > > particular column. The result is a filtered set of rows. Using VBA, > > > > what > > > > is the easiest way to, > > > > a) tell if any rows with data are in the filtered set (i.e. did the > > > > filter find anything that met the criteria)? > > > > b) get a count of how many rows with data, are shown in the filtered > > > > set? > > > > c) loop through rows/columns/cells in the filtered set? > > > > > > > > A bit of background. I write VBA for Project and in Project all one > > > > needs to do to determine if a filter shows anything is to check for an > > > > activeselection > 0. Looping is very simple - just loop through all > > > > tasks/resources in the activeselection. > > > > > > > > John > > > > Project MVP > > > > > > |