Clearing a range on another worksheet

Yoepy

New Member
Joined
Feb 22, 2015
Messages
44
I'd appreciate some help on probably a simple problem.

I want to clear the contents on Sheet1 of a range from row 15 to 51 but the column number is a variable based on a Input box (Dim myValue As Integer).

When I run the VBA from the Sheet1 it works fine as follows

Code:
ActiveSheet.Range(Cells(15, (myValue)), Cells(51, (myValue))).ClearContents

But, I need to run it from another sheet.

Code:
Worksheets("Sheet1").Range(Cells(15, (myValue)), Cells(51, (myValue))).ClearContents

This doesn't work. What an I doing wrong?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows
Please post ALL the code you are using
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows
Make sure you are using the col Number and not the letter !

Code:
myval = InputBox("which column number do you want to use ??")
Worksheets("Sheet1").Range(Cells(15, myval), Cells(51, myval)).ClearContents
 

Yoepy

New Member
Joined
Feb 22, 2015
Messages
44
Code:
Dim myValue As Integer 
myValue = Application.InputBox("Which range to delete?")
Worksheets("Sheet1").Range(Cells(15, (myValue)), Cells(51, (myValue))).ClearContents
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

did you try my version ??
 

Yoepy

New Member
Joined
Feb 22, 2015
Messages
44
Yes I did Michael. But it's still not working. I'm in the process of deleting unrelated code to see if something else is causing the problem.


Thanks though.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hmm, works fine for me !
1. are you on the correct sheet
2. Is the sheet protected
3. Is the code in a module, worksheet, or This Workbook
4. how are you firing the code
 

Yoepy

New Member
Joined
Feb 22, 2015
Messages
44
Thanks Michael. Your's did work (as did mine) when I fixed an unrelated piece of code.

Appreciate you pointing me in the right direction - that is look for something else.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows
Glad to help and thx for the feedback...(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top