 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Autofilter-Filtered List Scan
Hi All I use an autofilter on my 'database sheet' of records to selectively filter the required data. I currently scan the visible filtered list and transfer data to my report and chart sheets. I scan from the first row to the last filtered and visible row. Testing each row using Not EntireRow.Hidden. This works fine. What concerns me is that as my 'database sheet' get longer, that scanning ALL rows, and selecting the not hidden rows is a big overhead and things will slow down. IS there a better way? For example copying the filtered list to an array and scanning this - I am not sure how to do this.  SignatureRegards, Nigel nigelnospam@9sw.co.uk Manually, you can select that visible range and copy|paste to a new location. Excel (after xl95) will only copy the visible cells. It's kind of like: selecting the range edit|goto|special|visible cells only edit|copy then paste In code: Dim HowManyVisRows as long dim VisRng as range With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible).cells.count - 1 if howmanyvisrows > 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else 'do what you want end if Untested, uncompiled--watch for typos. > Hi All > I use an autofilter on my 'database sheet' of records to selectively filter [quoted text clipped - 17 lines] > Nigel > nigelnospam@9sw.co.uk SignatureDave Peterson Hi Dave Thanks, I read this to mean I could copy the data (visible rows only) to a new a range which I can then read into my reports. In the VisRng just created there maybe several rows and columns, I presume use something like Dim Cell as Range For Each Cell in Visrng Cell.Offset(0,0).Value = Row 1 / Column 1 Cell.Offset(0,1).Value = Row 1 / Column 2 Next Is this correct? SignatureRegards, Nigel nigelnospam@9sw.co.uk > Manually, you can select that visible range and copy|paste to a new > location. [quoted text clipped - 55 lines] >> Nigel >> nigelnospam@9sw.co.uk I don't understand your question. The code I suggested (as well as the manual technique) can be used to copy to a new location. This is the portion that would do the copy: if visrng is nothing then 'warning message??? else visrng.copy _ destination:=worksheets("Somesheetnamehere").range("a1") end if > Hi Dave > Thanks, I read this to mean I could copy the data (visible rows only) to a [quoted text clipped - 80 lines] > > > > Dave Peterson SignatureDave Peterson What I was asking was can I read thru the Range rather than just copy it to a new location. I have tried using VisRng.Cells(x,y), where x and y are the row and column in the range. On another point, I tried the code you posted and I get inconsistent results depending on the filter setting. Sheet 1 contains some test data as follows the highlighted row marked c1, c2, c3 and c4 has the autofilter. c1 c2 c3 c4 1 2 3 4 1 22 33 44 2 333 444 555 2 3333 4444 5555 1 33333 44444 55555 using the range setting ..... Set VisRng = .Offset(1, 0).Resize(.Rows.Count - 1)_ .Cells.SpecialCells(xlCellTypeVisible) and testing the range rows count using VisRng.Rows.Count with no filter I get 5 as expected with filter in column 1 set to '2' I get 2 as expected with filter in column 1 set to '1' I get 2 - not expected there should be three! Do you know why? SignatureRegards, Nigel nigelnospam@9sw.co.uk >I don't understand your question. > [quoted text clipped - 103 lines] >> > >> > Dave Peterson Hi Dave I have been investigating this issue and discover that the construct With Sheets(1).AutoFilter.Range Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With Results in the selected range extent being as far down as the first hidden row less the header. All rows that are not hidden after this are ignored! True in both xl2003 and xl2007 SignatureRegards, Nigel nigelnospam@9sw.co.uk > What I was asking was can I read thru the Range rather than just copy it > to a new location. I have tried using VisRng.Cells(x,y), where x and y are [quoted text clipped - 134 lines] >>> > >>> > Dave Peterson The problem does not lye with the VisRng setting but the use of the function VisRng.Rows.Count This only shows the count for the first n rows that are not hidden. Any ideas how I can access ALL rows in VisRng SignatureRegards, Nigel nigelnospam@9sw.co.uk > Hi Dave > I have been investigating this issue and discover that the construct [quoted text clipped - 148 lines] >>>> > >>>> > Dave Peterson Actually, that doesn't select anything. If you added: visrng.select I would bet that it selects the visible rows in that range. > Hi Dave > I have been investigating this issue and discover that the construct [quoted text clipped - 163 lines] > >> > >> Dave Peterson SignatureDave Peterson If you want to loop through each of the visible rows, you could do this: > > if howmanyvisrows > 0 then > > 'avoid the header and come down one row [quoted text clipped - 3 lines] > > set visrng = nothing > > end if Notice that the .resize() portion has been changed to a single column. Then you can loop through each of those cells in that range: dim myCell as range dim HowManyCols as long With worksheets("somesheetname").autofilter.range howmanycols = .columns.count 'subtract one for the header. ... For each mycell in visrng.cells 'to copy that row mycell.resize(1, howmanycolumns).copy 'to check the value of a different cell in that same row if mycell.offset(0,5).value = 33 then ... next mycell I don't have a guess what happened with your filter. I'd try it again. > What I was asking was can I read thru the Range rather than just copy it to > a new location. I have tried using VisRng.Cells(x,y), where x and y are the [quoted text clipped - 144 lines] > > > > Dave Peterson SignatureDave Peterson somerange.rows.count will return the number of rows in the first area--not the total number of rows in all the range. For your autofilter.range, you'd want something like: msgbox somesheet.autofilter.range.columns(1) _ .cells.specialcells(xlcelltypevisible).cells.count > What I was asking was can I read thru the Range rather than just copy it to > a new location. I have tried using VisRng.Cells(x,y), where x and y are the [quoted text clipped - 144 lines] > > > > Dave Peterson SignatureDave Peterson Many thanks for your help, I have got it now! SignatureRegards, Nigel nigelnospam@9sw.co.uk > somerange.rows.count > will return the number of rows in the first area--not the total number of [quoted text clipped - 161 lines] >> > >> > Dave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.