Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Clear Contents Of Range In Another Sheet

0

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()
'COPY EACH STORES DATA FROM "DATAEACHSTORE" TO THE "DATA VIEW "SHEETS
'RICHARDSON
 Dim lrow As Long
 Dim Rng As Range
 Worksheets("DATA VIEW RICH STORE").Rng("B6:i2000").Select
        Selection.ClearContents
        Selection.ClearFormats
End Sub
excelmacros-vba
MS Office Version: 2013
posted by: carroll (rep: 16) on Tue Jan 29, 2019 at 2:32 pm
Answer
Discuss

Answers

0
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
Rng.Clear

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

posted by: don (rep: 1989) on Tue Jan 29, 2019 at 3:11 pm
Discuss


Answer the Question

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