Clear Contents Of Range In Another Sheet


I am in sheet "dataeachstore" and want to go to sheet "rich data view"

and clear contents of range "b6:i2000

seems like this code would work??  I seem to have a problem when I am in a sheet and want to go to another sheet and do something.  A previous answer gave me code using "with" and "end with"  I tried using here but it didn't make any difference.  I guess I just don't know how to go to another sheet and do something and then return to the sheet I started in.

Thanks  Carroll

Private Sub CommandButton7_Click()
 Dim lrow As Long
 Dim Rng As Range
 Worksheets("DATA VIEW RICH STORE").Rng("B6:i2000").Select
End Sub


Selected Answer

You don't actually have to navigate to or "Select" a range or worksheet that you want to do something with and, in fact, that makes life more difficult in the Macro world.

You're very close though and I commend you for your efforts!

Try this line of code instead:

Sheets("DATA VIEW RICH STORE").Range("B6:i2000").Clear

You can replace all of the code in your macro with this single line.

Rng vs Range Notes

Here are some notes on the code that you made that might help you understand a little more of what's going on.

In your code, you created a variable called Rng but then you tried to use it like the Range method. These two things are totally separate, and you could have actually named the variable Rng whatever you wanted, it doesn't matter because it does not do the same thing as the Range method. The Rng variable that you made will hold a reference to a range but it cannot create that reference itself. 

To use the Rng variable with the range reference that you want, you could have used it like this:

Dim Rng As Range

' Put the range into the Rng variable
Set Rng = Sheets("DATA VIEW RICH STORE").Range("B6:i2000")

' Clear the range using the range reference stored in the Rng variable

This will do the same thing as the line of code that I put at the top of this answer.


Answer the Question

You must create an account to use the forum. Create an Account or Login