PDA

View Full Version : SpecialCells(xlCellTypeConstants).select shows unexpected behavior (for me)



stranno
01-01-2016, 05:51 PM
Hi,

All data containing cells in a sheet are being selected due to the next code line: Range("E2").SpecialCells(xlCellTypeConstants).Select. Why?

Stranno

Paul_Hossler
01-02-2016, 06:14 AM
Excel tries to help if you do a .SpecialCells of a single cell and assumes the entire sheet or at least .UsedRange (I think)

Q: Why are you using a single cell (E2)?



Sub test2a()

Dim r As Range
Set r = Range("E2").SpecialCells(xlCellTypeConstants)
MsgBox r.Address


Set r = Range("E2")
MsgBox r.Address


Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
MsgBox r.Address

End Sub

rollis13
01-02-2016, 08:48 AM
In macro test1 this line possibly should be with an extra ":E" like this and there must be at least a title in cell E1:
Range("E2:E" & LR).SpecialCells(xlCellTypeConstants).Select

stranno
01-02-2016, 09:58 AM
Excel tries to help if you do a .SpecialCells of a single cell and assumes the entire sheet or at least .UsedRange (I think)

Q: Why are you using a single cell (E2)?



Sub test2a()

Dim r As Range
Set r = Range("E2").SpecialCells(xlCellTypeConstants)
MsgBox r.Address


Set r = Range("E2")
MsgBox r.Address


Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
MsgBox r.Address

End Sub



I don't Paul. But sometimes a range consists of only 1 cell. In this case it depends on the users input. I wrote the following code lines and i wondered why the search took such a
long time. Turned out that LR = 2 and rng contained hundreds of thousands cells. Something to think about next time.

LR = ActiveSheet.Range("E2:E" & LR).End(xlUp).Row
Set rng = ActiveSheet.Range("E2:E" & LR).SpecialCells(xlCellTypeConstants)
Set c = rng.Find(What:=Me.TextBox11.Value, LookAt:=xlWhole, LookIn:=xlValues)

snb
01-02-2016, 10:01 AM
Apparently for VBA


Sub M_snb()
cells.SpecialCells(2).Select
End Sub

and

Sub M_snb()
Cells(1).SpecialCells(2).Select
End Sub

and

Sub M_snb()
Cells(1,7).SpecialCells(2).Select
End Sub

are identical.
Ergo:
1 if the range consists of 1 cell only the range is considered to refer to the entire worksheet.
2 using the specialcells property of a single cell is considered by VBA to be nonsense (and I agree)

stranno
01-02-2016, 10:19 AM
Sorry rollis13 you're absolutely right. My example file is very messy and carelessly. This one is better.

stranno
01-02-2016, 10:38 AM
Apparently for VBA


Sub M_snb()
cells.SpecialCells(2).Select
End Sub

and

Sub M_snb()
Cells(1).SpecialCells(2).Select
End Sub

and

Sub M_snb()
Cells(1,7).SpecialCells(2).Select
End Sub

are identical.
Ergo:
1 if the range consists of 1 cell only the range is considered to refer to the entire worksheet.
2 using the specialcells property of a single cell is considered by VBA to be nonsense (and I agree)

Mostly i don't know in advance how many cells are part of a range (as i said before it often depends on the users input).
But the main thing is, I wasn't aware of this behaviour. Since you use the word "Apparently" i guess it might be new for you too.

snb
01-02-2016, 02:42 PM
Please do not quote !

If you don't know...., how can you use Range("E2").SpecialCells(xlCellTypeConstants).Select :confused:

Paul_Hossler
01-02-2016, 03:31 PM
Mostly i don't know in advance how many cells are part of a range (as i said before it often depends on the users input).
But the main thing is, I wasn't aware of this behaviour. Since you use the word "Apparently" i guess it might be new for you too.



Not tested but you can catch a one cell selection



If Selection.Cells.Count = 1 Then
If Not Selection.HasFormula Then
If Len(Selection) > 0 Then
........




'Apparently' since no one but Bill Gates really knows what happens inside Excel.


I've been caught many times

stranno
01-02-2016, 03:39 PM
snb, Of course i don't use code like Range("E2").SpecialCells(xlCellTypeConstants).Select in my procedures. That would be a silly thing to do, wouldn't it?
i do Use: Set rng = ActiveSheet.Range("E2:E" & LR).SpecialCells(xlCellTypeConstants) (#4)

"Range("E2").SpecialCells(xlCellTypeConstants).Select" was meant to demonstrate that it has the same meaning (outcome) as
ActiveSheet.Range("E2:E" & LR).SpecialCells(xlCellTypeConstants) if LR = 2. That's Obvious right?

What surprised me was that all cells (holding constants) are being selected if LR = 2.

By the way:I noticed before that you have a strong preference to misunderstand my points.:motz2:
Maybe I should formulate more clearly.:)

stranno
01-02-2016, 03:56 PM
Thanks Paul, but i know how to parry it.
The thing was that i did not know that all cells were selected (snb, in fact i don't select them at all. It's just for demo purposes.)

Paul_Hossler
01-02-2016, 04:38 PM
What is the user going to do to run your macro on?

Manually a) select a cell or b) block of cells or c) blocks of cells?


What is your macro going to do with the cell(s)?

stranno
01-03-2016, 04:00 AM
I am not sure what you mean. But when the user clicks on a commandbutton a macro starts a search after a text string in column E. Column E has a field name in E1. The other cells in this column may contain text strings or not. This depends on the users input and the period of time in which this program has been used. After a period of time ( a year), column E might have been filled up to cell E80.000. This column E is part of a hidden worksheet which contains stored data. But at the very start Cell E3 may be empty. And if this is the case (LR = 2), the whole worksheet is involved in the search range because i formulated the search range as Range("E2:E" & LR).SpecialCells(xlCellTypeConstants)

snb
01-03-2016, 04:13 AM
So there is no reason to use 'select' (which is seldom wise), but


for each it in columns(5).specialcells(2)
if it.row>1 then ......
next

stranno
01-03-2016, 04:56 AM
A few things:

I think that "rng.Find(What:=Me.TextBox11.Value, LookAt:=xlWhole, LookIn:=xlValues)" is a lot faster than
For Each it In columns(5).specialcells(2)
If it.row>1 Then ......
Next
(I have tested this by calculating the elapsed time)

Selecting the range was only meant to show what can happen if the search range contains only 1 cell.

I know how to avoid this unexpected and unwanted behaviour.

But the reason i started this thread was my astonishment about the fact that the whole sheet became involved in
the search range if Range("E2:E" & LR).SpecialCells(xlCellTypeConstants) contains only 1 cell.

I wanted to share this with you. And hoped that somebody could explain why this happens. That's all.

snb
01-03-2016, 05:15 AM
'Find' is very slow compared to:


sn=columns(5).specialcells(2).offset(1).specialcells(2)
for j=1 to ubound(sn)
- - - - - - - -
next

stranno
01-03-2016, 05:46 AM
I'll test your suggestion. If you're right this thread wasn't for nothing after all.:)

stranno
01-03-2016, 06:28 AM
snb,
Take a look at the attached workbook.
It appears that the find method is approximately ten times faster than your method.

snb
01-03-2016, 10:46 AM
You are comparing apples with pears:


Sub prep()
Application.ScreenUpdating = False
[A1:A100000] = "snn"
Cells(999999, 1) = "snb"
End Sub

Sub Test1()
sn = Columns(1).SpecialCells(2).Offset(1).SpecialCells(2)

t1 = Timer
For j = 1 To 800
For jj = 1 To UBound(sn)
If sn(jj, 1) = "snb" Then Exit For
Next
Next

MsgBox Timer - t1
End Sub

Sub Test2()
Dim sn As Range
Dim c As Range

Set sn = Range("A1:A1000000")

t1 = Timer
For j = 1 To 800
Set c = sn.Find("snb")
Next

MsgBox Timer - t1
End Sub

I didn't suggest to use an array to ignore it later in the code....

stranno
01-03-2016, 03:26 PM
Impressive but this is even faster:

Sub Test3()
Dim sn As Range
t1 = Timer
Set sn = Range("A1:A1000000")
For j = 1 To 800
r = WorksheetFunction.Match("snb", sn, 0)
Next
MsgBox Timer - t1
MsgBox r
End Sub

But the find method is good enough for me in this case.

Still good to know that "sn = Columns(1).SpecialCells(2).Offset(1).SpecialCells(2)" is a very fast method as well.

stranno
01-04-2016, 04:15 AM
Snb, after all your solution is also useful. Is offset(1) meant to exclude the column head, or has it another function? I don't understand it fully.

snb
01-04-2016, 05:02 AM
- doesn't surprise me ;)
- yes it is

but this would do as well:


Sub Test1()
sn = Columns(1).SpecialCells(2)

For j = 2 To UBound(sn)
If sn(jj, 1) = "snb" Then c00=c00 & vblf & j
Next

msgbox "rows with 'snb' " & c00
End Sub
This method is especially helpful identifying multiple instances of the same value (Find nor Match accomplish that).

Aflatoon
01-04-2016, 05:15 AM
Of course that doesn't work if there are any blank cells within your data.

stranno
01-04-2016, 02:59 PM
ok thanks