Delete Content Using Variable in Range Function

Question

Run-time error '1004' Method 'Range' of object '_Global' failed

I am getting above error as soon as I execute below sequence. My objective here is to delete content of all cells falling in range of current cell till C200.

Posit2.Select
Sheets("Sheet1").Cells(53, 1).Value = ActiveCell.Address      ' $C$52 is output
Range(ActiveCell.Address, c200).ClearContents

hevilp · Accepted Answer · 2017-10-29 11:33:31Z

1

Your line is wrong,

Range(ActiveCell.Address & " : C200").ClearContents

Is right

edited Oct 29, 2017 at 11:33

answered Oct 29, 2017 at 11:08

hevilp

571 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-10-29 14:37:32Z

0

You can also try replacing your error line to:

Range(ActiveCell, Cells(200, "C")).ClearContents

answered Oct 29, 2017 at 14:37

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Comments

Calico · Accepted Answer · 2017-10-29 15:55:48Z

0

Eventually you will want to move away from selecting. Assuming Posit2 is a reference to C52 on Sheet1 in your example then

Dim Posit2 as Range

set Posit2 = Worksheets("Sheet1").range("C52")

With Posit2.parent
  .Range(Posit2, .Cells(200,"C")).ClearContents
End With

First two lines are for illustration as you should already have code that does that. Also, by referencing the sheet, Sheet1 would not need to be the activesheet.

edited Oct 29, 2017 at 15:55

Calico

4161 gold badge4 silver badges17 bronze badges

answered Oct 29, 2017 at 14:51

T. Ogilvy

11 bronze badge

Collectives™ on Stack Overflow

Delete Content Using Variable in Range Function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related