1

I am using below code to clear the content from A2 cell till last. this code working fine if the active sheet is TimeStampWork. But If I on any other sheet, I am getting Error 1004, Method 'Range' of object '_Worksheet' failed error. How can I run the same code eventhough sheet is not active ?

Sub test1()
Dim ForClr As Worksheet
Set ForClr = Workbooks("Main.xlsm").Sheets("TimeStampWork")
ForClr.Range("A2", Range("A2").End(xlDown)).ClearContents
End Sub
acr
  • 1,674
  • 11
  • 45
  • 77
  • Is this macro in a normal code module or on a worksheet module? – vacip Aug 28 '17 at 13:42
  • Are you trying to run this code on all sheets or just in "TimeStampWork"? – Rafael Matos Aug 28 '17 at 13:42
  • 2
    Also **[Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells)** –  Aug 28 '17 at 13:55

2 Answers2

1

In this one of your Range objects is unqualified, so is referencing the active sheet:

ForClr.Range("A2", Range("A2").End(xlDown)).ClearContents

You should write instead

ForClr.Range(ForClr.Range("A2"), ForClr.Range("A2").End(xlDown)).ClearContents
Erik Westwig
  • 713
  • 4
  • 16
0

try this

Sub test1()
  Dim ForClr As Worksheet
  Set ForClr = Sheets("TimeStampWork")
  ForClr.range(ForClr.range("A2"), ForClr.range("A2").End(xlDown)).ClearContents
End Sub
vacip
  • 5,246
  • 2
  • 26
  • 54
Volkan Yurtseven
  • 425
  • 3
  • 15
  • 2
    True. A little explanation would help though. [A similar discussion.](https://stackoverflow.com/questions/31907394/range-object-why-musnt-i-use-a-sheet-sometimes) The first `"A2"` in OP's code is referencing to cell A2 on the active sheet, and that makes the rest an impossible range declaration spanning through multiple sheets. – vacip Aug 28 '17 at 13:50
  • 1
    @vacip the *first* "A2" in OP's code *is* qualified to `ForClr`, it's the second that is unqualifed `Range("A2")` which will be on the ActiveSheet. – David Zemens Aug 28 '17 at 13:52
  • i didn't think it requires any further explanation. it is quite obvious and conceivable. – Volkan Yurtseven Aug 28 '17 at 13:56
  • 1
    @ExcelinEfendisi what's obvious to you or me is not likely to be obvious to beginners or those otherwise unfamiliar with the object model or its quirks. The best answers here usually includes at least some brief diagnosis of the problem/error source along with an explanation of how the solution "works". – David Zemens Aug 28 '17 at 14:04
  • Fair enough David,so be it – Volkan Yurtseven Aug 28 '17 at 14:09
  • 1
    Downvoting, for the reason @DavidZemens pointed out. There are more than enough "try this {code dump}" answers on this site - *too many* of them, actually. These answers don't teach anything, promote copy-pasta code, and only cause more [duplicate] questions to be asked when the OP hits the next roadblock because they haven't learned anything from any of the "try this {code dump}" answers they are getting here. Put some effort into your answers, and you'll be rewarded for making the Internet a better place. Or don't, and keep the wheel of ignorance spinning. – Mathieu Guindon Aug 28 '17 at 14:59