Type Mismatch on range= selection

Question

I'm running a fairly simple code and getting runtime error 13 (type mismatch). I've run macros similar to this many times before and I'm not sure what's causing this.

    Private Sub CommandButton21_Click()
Dim source As Range, source2 As Range, ICCP As Range, ICCP2 As Range, Devtype As Range, Devtype2 As Range, cell As Range, cell2 As Range, destination As Range, i As Long, j As Long


Set source = Sheet2.Columns(1)
Set source2 = Sheet3.Columns(1)
Set ICCP = Sheet2.Columns(2)
Set ICCP2 = Sheet3.Columns(2)
Set Devtype = Sheet2.Columns(4)
Set Devtype2 = Sheet3.Columns(4)


If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range first."
    Exit Sub
Else
    Set cell = Selection
End If
If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range first."
    Exit Sub
Else
    Set cell2 = Selection
End If

Set destination = Sheet1.Columns(1)

For Each cell In source
Select Case cell.Value
Case "BHPL", "CAISO", "CECD", "CFE", "CSU", "EPE", "FEUS", "HHWP", "IID", "PNW", "POPD", "PRPA", "PSCO", "ROSA", "ROSP", "SCE", "SDGE", "SRSG", "SWTC", "TEP", "TSGT", "VEA", "WALC"
Case Else
cell.EntireRow.Delete
End Select
Next cell

For Each cell2 In source2
Select Case cell2.Value
Case "BHPL", "CAISO", "CECD", "CFE", "CSU", "EPE", "FEUS", "HHWP", "IID", "PNW", "POPD", "PRPA", "PSCO", "ROSA", "ROSP", "SCE", "SDGE", "SRSG", "SWTC", "TEP", "TSGT", "VEA", "WALC"
Case Else
cell.EntireRow.Delete
End Select
Next cell2
 End Sub

That's the updated code, debugger is catching it at this line:

Case "BHPL",...

Updating your Post to change the question to address a new error exposed by a given answer is not how this site works. I've rolled back to the previous version. If you still need help, ask a new (good quality) question, although in this case you already have the anser in the link Sid gave you — chris neilsen, Sep 09 '15 at 20:52

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

Your IF Cond is incorrect.

You can't write it as If cell.Value = "BHPL" Or "CAISO"...

Use this

If cell.Value = "BHPL" Or cell.Value = "CAISO" Or '..And So On..

Better still use a Select Case

Select Case cell.Value
    Case "BHPL", "CAISO", "CECD"  '..And So On..
    Case Else
        cell.EntireRow.Delete
End Select

As far as actual deleting is concerned, delete in a reverse loop or use a temp range as shown HERE

Edit

Another suggestion

When ever you are using the Selection object as shown below

Set cell = Selection

It is better to check if it is a range.

'~~> Check if what the user selected is a valid range
If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range first."
    Exit Sub
Else
    Set cell = Selection
End If

Better still. Avoid the use of Selection. You may want to see THIS

edited May 23 '17 at 12:22

Community

1
1

answered Sep 08 '15 at 22:21

Siddharth Rout

147,039
17
206
250

Still getting that same error, this time debugger highlights: Case "BHPL", "CAISO",... – Errorum Sep 08 '15 at 22:34
I hope you removed those extra DOTS in the end? They are for demonstration puprpose – Siddharth Rout Sep 08 '15 at 22:35
Give me a little credit; I didn't include your dots, haha. I may work on removing the selection if I get it working, but my priority is getting this macro to work first, before worrying about reusability. I replaced my cell=selection parts with your if statement, and everything else is identical... same problem, same place :( – Errorum Sep 08 '15 at 22:49
Can you please update your question with the current code that you are using? – Siddharth Rout Sep 08 '15 at 22:52
In this case where `source` is a whole column the `For Each cell In source` returns the whole column into `Cell`. Use `For Each cell In source.Cells` instead. Also worth noting this will take a _long_ time to run. Reduce the `source` range to used cells only (and once this is done the `.Cells` is no longer needed). – chris neilsen Sep 09 '15 at 00:21
@chrisneilsen: Long Time No See! :) Yeah that is a good point. I am still waiting for the user to come back on the basic errors first – Siddharth Rout Sep 09 '15 at 06:21
@chrisneilsen: You were right, adding the .cells made the type mismatch go away, and it was super slow. However, it seemed to just delete rows at random. Some were the ones I specified, others were those I wanted to keep. Any more ideas? – Errorum Sep 09 '15 at 17:34
1

The answer to your question is "Did you see the first link in my answer above on how to delete the rows?" You are still deleting the rows from top to down. – Siddharth Rout Sep 09 '15 at 17:46

Type Mismatch on range= selection

1 Answers1