SpecialCells(xlCellTypeConstants).select shows unexpected behavior (for me)

[stranno]

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]

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]

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]

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]

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]

Sorry rollis13 you're absolutely right. My example file is very messy and carelessly. This one is better.

[stranno]

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]

Please do not quote !

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

[Paul_Hossler]

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]

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.
Maybe I should formulate more clearly.

[stranno]

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]

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]

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]

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]

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]

'Find' is very slow compared to:

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

[stranno]

I'll test your suggestion. If you're right this thread wasn't for nothing after all.

[stranno]

snb,
Take a look at the attached workbook.
It appears that the find method is approximately ten times faster than your method.

[snb]

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]

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.