Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Help! Have the following snippet, am running into a problem were the
autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Hi George,
I did some testing on this. The following did not work because it starts at the first visible cell and increments by 1 and includes all addresses after the first visible cell. Not what I would have expected:- iMax = ASN.Count For i = 1 To iMax MsgBox ASN(i).Address Next i However, this method did work. It only displays the addresses of the visible cells. Hopefully using a 'For Each loop' will solve your problem:- For Each c In ASN MsgBox c.Address Next c Regards, OssieMac "G Lykos" wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
OssieMac, thanks for your suggestion. I'll take a look at your alternative
structure, but know that there is a fair amount of subsequent logic that might then need significant restructuring. Out of curiosity, if you still have your test data - does the selection set (using SpecialCells) element count coincide correctly with the number of selection set elements? In my case, the several invisible elements were embedded in the middle of the original data set, and the selection using SpecialCells pulled in all elements, even though the count reflected only the visible elements in the original data set. As the first element of my original data set is visible, what I encountered is potentially consistent with what you observed with yours. I guess what you may be suggesting is that the collection index access method, ASN(i), is flawed, while the collection count method, ASN.count, is not. If this is a bug, then I'll be curious to see if it also existed in Excel 2003 SP2, SP3 having just been released and what I'm on. George "OssieMac" wrote in message ... Hi George, I did some testing on this. The following did not work because it starts at the first visible cell and increments by 1 and includes all addresses after the first visible cell. Not what I would have expected:- iMax = ASN.Count For i = 1 To iMax MsgBox ASN(i).Address Next i However, this method did work. It only displays the addresses of the visible cells. Hopefully using a 'For Each loop' will solve your problem:- For Each c In ASN MsgBox c.Address Next c Regards, OssieMac "G Lykos" wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Although on second thought, if the SpecialCells Select were working
properly, then you wouldn't expect that the collection would have the invisible cells in it in the first place - unless the selection doesn't truly form a separate collection of data, but rather creates pointers into a "visible" subset (well, if it worked correctly) of the original data. "G Lykos" wrote in message ... OssieMac, thanks for your suggestion. I'll take a look at your alternative structure, but know that there is a fair amount of subsequent logic that might then need significant restructuring. Out of curiosity, if you still have your test data - does the selection set (using SpecialCells) element count coincide correctly with the number of selection set elements? In my case, the several invisible elements were embedded in the middle of the original data set, and the selection using SpecialCells pulled in all elements, even though the count reflected only the visible elements in the original data set. As the first element of my original data set is visible, what I encountered is potentially consistent with what you observed with yours. I guess what you may be suggesting is that the collection index access method, ASN(i), is flawed, while the collection count method, ASN.count, is not. If this is a bug, then I'll be curious to see if it also existed in Excel 2003 SP2, SP3 having just been released and what I'm on. George "OssieMac" wrote in message ... Hi George, I did some testing on this. The following did not work because it starts at the first visible cell and increments by 1 and includes all addresses after the first visible cell. Not what I would have expected:- iMax = ASN.Count For i = 1 To iMax MsgBox ASN(i).Address Next i However, this method did work. It only displays the addresses of the visible cells. Hopefully using a 'For Each loop' will solve your problem:- For Each c In ASN MsgBox c.Address Next c Regards, OssieMac "G Lykos" wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Hi George,
SpecialCells has a limit of 8192 areas, could that be the problem. If so work with chunks of up to 16000 rows at a time in the entire selection. Regards, Peter T "G Lykos" wrote in message ... Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem - BUGS
OssieMac, thought some more about what you said, realized that this is
"deja-vu all over again", and means that there are still basic flaws in the count and index methods when used with selections involving invisible cells. Bugs 1 (which you articulated) and 2: Create a vertical list of header and three data cells. Set an autofilter that hides the first and third cells. Set a range R using SpecialCells(xlCellTypeVisible).Select to the three cells: Debug.Print R(1) yields the second (visible) cell, Debug.Print R(2) yields the third (invisible) cell [ouch], Debug.Print R(3) yields a fourth cell, outside of the selection [ouch]. Bug 3 (not mentioned before): Create a vertical list of header and one data cell. Set a range R using SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count = 1. Now set an autofilter that hides the cell, set range R using SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count = 16776960 [ouch]. I just checked this in Excel 97. It's really hard to believe that these same basic bugs are alive and well in Excel 2003 SP3. Wonder if Excel 2007 is the same. Regards, George "OssieMac" wrote in message ... Hi George, I did some testing on this. The following did not work because it starts at the first visible cell and increments by 1 and includes all addresses after the first visible cell. Not what I would have expected:- iMax = ASN.Count For i = 1 To iMax MsgBox ASN(i).Address Next i However, this method did work. It only displays the addresses of the visible cells. Hopefully using a 'For Each loop' will solve your problem:- For Each c In ASN MsgBox c.Address Next c Regards, OssieMac "G Lykos" wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Peter, thanks for the information and suggestion. In this case, there are
670 cells in the list, of which two are filtered out. Please see my separate post just now to OssieMac describing three apparent BUGS related to invisible cells in a selection - if you should have further information, I'm certainly interested. Regards, George "Peter T" <peter_t@discussions wrote in message ... Hi George, SpecialCells has a limit of 8192 areas, could that be the problem. If so work with chunks of up to 16000 rows at a time in the entire selection. Regards, Peter T "G Lykos" wrote in message ... Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
In a light test I cannot recreate your discrepancy.
Maybe this is misleading you - determined by stepping through debug.printing ASN(n). If ASN is a multi-area range, which no doubt it is, you need to do for each rArea in ASN.Areas for each cel in rArea debug.? cel.address, cel.value or for n = 1 to rArea.count debug.? rArea(n).whatever next: next Regards, Peter T "G Lykos" wrote in message ... Peter, thanks for the information and suggestion. In this case, there are 670 cells in the list, of which two are filtered out. Please see my separate post just now to OssieMac describing three apparent BUGS related to invisible cells in a selection - if you should have further information, I'm certainly interested. Regards, George "Peter T" <peter_t@discussions wrote in message ... Hi George, SpecialCells has a limit of 8192 areas, could that be the problem. If so work with chunks of up to 16000 rows at a time in the entire selection. Regards, Peter T "G Lykos" wrote in message ... Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
I have just got back to my computer and done some more testing and the only
way I can get it to work is the method that I posted before using For Each loop. I have tested in both xl2002 and xl2007. Just as an added extra, I have previously had problems in the interactive mode actually selecting the visible cells in a filtered list for copy and paste. All cells get selected and pasted. To get around it I have had to Go to Special and select visible Cells only. This is an intermittent problem that Ron de Bruin MVP said "more people have experienced this problem". Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Whilst the single For-Each loop works personally I prefer to loop areas when
dealing with a potential multi area range, either with For-To or For-Each, eg Sub test() Dim i&, j&, n& Dim rng As Range, rArea As Range, cel As Range Set rng = Range("A1:A3,A7:A10") ' the 5th cell in this range is A8, not A5 Debug.Print rng(5).Address ' A5 wrong ' this represents the 5th cell of the first area, which extends ' beyond the area n = 0 For Each cel In rng n = n + 1 If n = 5 Then Debug.Print cel.Address Next ' or n = 0 For Each rArea In rng For Each cel In rArea n = n + 1 If n = 5 Then Debug.Print cel.Address Next Next 'or n = 0 For i = 1 To rng.Areas.Count For j = 1 To rng.Areas(i).Count n = n + 1 If n = 5 Then Debug.Print rng.Areas(i)(j).Address Next Next End Sub I can't find any reference to the "intermittent problem" you say Ron de Bruin has referred to.. Regards, Peter T "OssieMac" wrote in message ... I have just got back to my computer and done some more testing and the only way I can get it to work is the method that I posted before using For Each loop. I have tested in both xl2002 and xl2007. Just as an added extra, I have previously had problems in the interactive mode actually selecting the visible cells in a filtered list for copy and paste. All cells get selected and pasted. To get around it I have had to Go to Special and select visible Cells only. This is an intermittent problem that Ron de Bruin MVP said "more people have experienced this problem". Regards, OssieMac |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
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. G Lykos wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
Hi Dave,
If you insert the following at 'do what you want' then I believe it demonstrates what George has been referring to. It appears that the cells cannot be referenced using VisRng(i). However, as per my previous post, For Each c In VisRng works. 'Following does not work. 'VisRng.Count is correct. 'Starts at correct cell but loops through 'all cells (including non visible) from 'that point until i VisRng.Count. For i = 1 To VisRng.Count MsgBox "Address = " & VisRng(i).Address & Chr(13) _ & "Value = " & VisRng(i).Value Next i 'This does work and only displays address 'and values of visible cells. For Each c In VisRng MsgBox "Address = " & c.Address & Chr(13) _ & "Value = " & c.Value Next c Regards, OssieMac "Dave Peterson" wrote: 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. G Lykos wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells Select problem
You're right. VisRng(i) won't exclude hidden cells.
OssieMac wrote: Hi Dave, If you insert the following at 'do what you want' then I believe it demonstrates what George has been referring to. It appears that the cells cannot be referenced using VisRng(i). However, as per my previous post, For Each c In VisRng works. 'Following does not work. 'VisRng.Count is correct. 'Starts at correct cell but loops through 'all cells (including non visible) from 'that point until i VisRng.Count. For i = 1 To VisRng.Count MsgBox "Address = " & VisRng(i).Address & Chr(13) _ & "Value = " & VisRng(i).Value Next i 'This does work and only displays address 'and values of visible cells. For Each c In VisRng MsgBox "Address = " & c.Address & Chr(13) _ & "Value = " & c.Value Next c Regards, OssieMac "Dave Peterson" wrote: 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. G Lykos wrote: Help! Have the following snippet, am running into a problem were the autofilter screens out two records in the data set but the selection set number of items and the selection set count are different! Selection.Autofilter Field:=110, Criteria1:="<Test" Range("A1").CurrentRegion.Sort , Header:=xlYes, _ Key1:=Range("DG1"), Key2:=Range("AZ1") Range("DG1").Offset(1).Select Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisi ble).Select Set ASN = Selection iMax = ASN.Count The final selection count, and iMax, reflect the correct number of visible cells. However, range ASN contains all cells, both visible and invisible, determined by stepping through debug.printing ASN(n). Why am I picking up invisible cells in the selection set, and how can the selection set count be different than the number of selection set elements?? A subsequent work-around was to copy the entire worksheet after filtering and sorting and paste it into a scratch worksheet, then set ASN there, but I'd much prefer to avoid that gyration. Any ideas? Windows XP, Excel 2003 SP3. Thanks! George -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpecialCells problem | Excel Programming | |||
Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select | Excel Programming | |||
How select/define cells with FIND method (maybe together with SpecialCells) | Excel Programming | |||
.SpecialCells(xlLastCell).Select | Excel Programming | |||
XP VBA: Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select | Excel Programming |