The reason is
SpecialCells "looks" at the
UsedRange when returning a range.
SpecialCells on an unused sheet will return
Runtime Error '1004'. No Cells Found. (As an aside always use error handling with SpecialCells)
When you change cell A1, that becomes the sheets
UsedRange, hence your Case 1 return of "A1"
The code below looks to return Range("A1:D4").SpecialCells(xlCellTypeBlanks) for
- A blank sheet - fails
- The sheet with A10 filled in -
- The sheet with A10 cleared -
The sheet with the usedrange reset (which is effectively 1) - fails
Sub Test() Dim ws As Worksheet Set ws = Sheets.Add On Error Resume Next 'blank sheet Debug.Print "Blank sheet " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'enter in A10 ws.[a10] = "test" Debug.Print "With A10 " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'clear a10 ws.[a10].ClearContents Debug.Print "With A10 cleared " & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address 'reset range ActiveSheet.UsedRange Debug.Print "With A10 cleared and range reset" & ws.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address On Error GoTo 0 End Sub
.SpecialCells work with the used range of a worksheet and not specific cells unless the specific cells fall within the used range.
Specialcells on an entirely new sheet will give you the error because the usedrange is just $A$1. If $A$1 is colored then you will not get an error as expected in CASE 2 above.
You have to use
Specialcells with utmost care. Here is an example which will not give you an error on a new sheet.
Sub Sample() If ActiveSheet.UsedRange.Column > 1 Or ActiveSheet.UsedRange.Row > 1 Then Debug.Print ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Address End If End Sub
Now type something in Cell D5. Say "Blah Blah"
Now run this code
Sub Sample() Debug.Print Sheet1.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address End Sub
You will notice that this will work because the range is within the UsedRange Address.
- SpecialCells method of Excel Range strange behaviour
- Excel VBA "Autofill Method of Range Class Failed"
- Does Excel have a built in method for parsing formulas? (ie: to obtain a list of included RANGE references)
- Strange behavior when assigning a VBA array to formulas of an excel range
- Error in Excel 2010, PasteSpecial method of Range Class failed within macro
- Excel VBA run time error 1004 method range of object _worksheet failed
- Run time error 1004 method range of object_global failed on a search in Excel with a named range
- VBA Excel Range method
- Strange behaviour of .item() method of Scripting.Dictionary class in VBA
- Excel VBA - Using Find method on a range of dates
- Excel VBA Activate method of Range class failed
- excel 2010 returning: Run-time error '1004': "PasteSpecial method of Range class failed
- Excel VBA select method of range class failed
- Excel VBA - Autofill method of range class failed
- Excel VBA Method 'ClearContents' of Object Range Failed
- Excel macro: method range of object _worksheet failed
- Excel VBA Macro - Copy method of range class failed & Insert method of range class failed
- Excel vba 1004 method of range class failed: Autofill to varying length column
- Excel VBA Clearcontents method of Range class failed
- Copy Method not Working when Range is set to a Variable in Excel VBA
- Simple Excel VBA macro: error 1004 method of Range class failed
- Run-time error '1004': Copy method of Range class failed Excel 2013 when adding a new pivot table
- excel vba method range of object _worksheet failed
- Selected range of Excel cells passed to an argument "as Variant" of a VBA function, then passed to an method of an ATL object in c++
- Excel VBA - Trying to Copy 300 Workbook Ranges into a Sheet Based on a Batch File List and Receiving "Pastespecial method of range class failed" Error
- Method Range of Object Worksheet failed excel VBA
- How to display a 1 or 2 dim varraint array() return from an excel UDF function in an excel range without Ctrl+Shift+Enter array forumla method
- Excel VBA Error: 1004 Method 'Range' of object '_Worksheet' Failed when Selecting the Range of a Variables Value
- Excel VBA - strange behaviour and poor performance when UDF wraps VLOOKUP
- Excel 2010 - Select method of range class failed
More Query from same tag
- "Path does not exist" error using CurrentProject.Path as default folder in FileDialog
- MS ACCESS: How to filter report using combo box and query criteria
- Set an If statement on a cell depending on the time frame
- Convert data to a datavalue code in excel vba
- Is there a way to count the number of characters per word for a string, returning values separated by a comma?
- VBA to open MyComputer at a specific folder
- How to retrieve JSON response using VBA?
- VBA EXCEL HTML - Scraping data from website within a frame, from a span
- hide comment the moment a cell is filled with data
- VBA excel IsError function for special case
- Applying VBA RIGHT to an entire column - Infinite Loop Issue
- Excel VBA: Part of code only runs when stepping through. Does not run if called from other subroutine or when continuing
- Refer to different sheet and defined variable in VBA vlookup
- Multiple If/Then Call options
- VBA user form that returns data to next empty row in excel spreadsheet and limit data type for date in specific textbox
- Of two identical VBA functions one suddenly returns a #NAME error
- Trouble with VBA Range.PasteSpecial xlPasteValues
- Rename selected sheets with names from a list
- Finding the filename with the latest modification date that fits a particular pattern
- Using Worksheet Macro to hide FALSE Row
- For Each c in Range r spitting out error when printing value to other sheet?
- VBA Object variable or with block
- Using VBA to parse text in an MS Word document
- Excel macro -Split comma separated entries to new rows
- How to Stop Error '91':object variable or with block variable not set
- Have a text box that call a procedure from my class in VBA
- Excel VBA, count letters which are bold and give an average
- Copy and paste picture from excel to word
- MS access table as centralised location for storing data
- Opening multiple documents via 'For Each' structure. VBA