Cell = Range("A1") Type mismatch

**NicholasL** · 03-23-2014, 01:31 PM

I'm trying to clear all cells on a sheet that are not in the Range between A1:F1

This is what my code looks like:

Sub ClearTable()
Sheets("Tables").Activate

Dim WorkRange As Range
Dim thisCell As Range

Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
    If Cell = Range("A1") Or Range("B1") Or Range("C1") Or Range("D1") Or Range("E1") Or Range("F1") Then
    Else
    Cell.Clear
    End If
Next Cell
End Sub

The idea was to go through each cell in WorkRange (which is every cell defined by the .UsedRange method -- which I'm assuming just means every non-empty cell in the sheet).

If the cell is in the range I don't want deleted then nothing happens, else clear the cell.

On the line "If Cell = Range("A1") Or Range("B1")...." It yields a Type Mismatch error.

Thoughts?

Thanks!

**AB33** · 03-23-2014, 01:43 PM

If cell = Range("A1") Or cell = Range("B1") Or cell = Range("C1") Or cell = Range("D1") Or cell = Range("E1") Or cell = Range("F1") Then

**NicholasL** · 03-23-2014, 02:45 PM

Thank you!
That worked fine, I didn't realize I had to be so explicit in the structure.

**HaHoBe** · 03-23-2014, 02:57 PM

Hi, NicholasL,

why not shorten the code to

Sub ClearTable2()

Dim WorkRange As Range
Dim thisCell As Range

With Sheets("Tables")
  Set WorkRange = .UsedRange
  For Each thisCell In WorkRange
    If Intersect(thisCell, .Range("A1:F1")) Is Nothing Then
      thisCell.Clear
    End If
  Next thisCell
End With

Set WorkRange = Nothing
End Sub

or

Sub ClearTable3()

Dim WorkRange As Range
Dim thisCell As Range

With Sheets("Tables")
  Set WorkRange = .UsedRange
  For Each thisCell In WorkRange
    Select Case thisCell.Address
      Case "$A$1", "$B$1", "$C$1", "$D$1", "$E$1", "$F$1"
      Case Else
        thisCell.Clear
    End Select
  Next thisCell
End With
Set WorkRange = Nothing
End Sub

Ciao,
Holger

**HaHoBe** · 03-24-2014, 01:58 AM

Hi, Hi, NicholasL,,

if the UsedRange only covers Columns A to F you may use

Sub ClearTable3()
Sheets("Tables").UsedRange.Offset(1, 0).ClearContents
End Sub

or

Sub ClearTable4()
With Sheets("Tables")
  .Range("A2:F" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End With
End Sub

Ciao,
Holger

Cell = Range("A1") Type mismatch

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Cell = Range("A1") Type mismatch

Re: Cell = Range("A1") Type mismatch

Re: Cell = Range("A1") Type mismatch

Re: Cell = Range("A1") Type mismatch

Re: Cell = Range("A1") Type mismatch

Thread Information

Users Browsing this Thread

Similar Threads

Instr() function giving error: "Type mismatch"

Excel 2010 (Run-time error '13' type mismatch) "Debug" and "Continue" Grayed out.

Excel 2007: Error "13 - Type Mismatch" when assigning a value to a cell.

[SOLVED] Merged cells cause "Runtime error 13 type mismatch" problem

[SOLVED] Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions