How many rows?
in [Programming]
|
Prev: Excel 2007 used with Oracle ADI
Next: rename message
From: John on 3 Aug 2007 23:55 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
From: Tom Ogilvy on 4 Aug 2007 00:42 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 "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 >
From: John on 4 Aug 2007 15:30 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 > >
From: Tom Ogilvy on 4 Aug 2007 17:00 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 "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 > > > >
From: Tom Ogilvy on 4 Aug 2007 20:16
Correction to my last post: Subtotal(2,range) is for Numbers - equivalent to Count, but counting only rows that are visible when a filter is applied Subtotal(3,range) is for non empty cells - equivalent to CountA, but counting only rows that are visible when a filter is applied But again, consult the EXCEL help (spreadsheet has focus when you choose help) on Subtotal for details. -- Regards, Tom Ogilvy "Tom Ogilvy" 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 > > > > "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 > > > > > > |