Excel Quick Question: End With

Posted on 2012-08-26
Medium Priority
Last Modified: 2012-08-26
Referencing a column
Hello All,

Currently I am using this code type for doing something with the values that are in each column B,C,D
Note: the number of elements in those columns are different  
But using the code this way, is throwing a error [runtime 1004]

With Worksheets("mySheet").Range([B2], [B65536].End(xlUp))
               Do something with the column values    
 End With

With Worksheets("mySheet").Range([C2], [C65536].End(xlUp))
              Do something with the column values    
 End With

With Worksheets("mySheet").Range([D2], [D65536].End(xlUp))
                Do something with the column values    
 End With

But when I put something like below (activate that sheet and do “With Range….”), it works
With Range([D2], [D65536].End(xlUp))
        Do something with the column values    
End With

But the problem is I don't want users to see that worksheet. Currently I am forced to activate that sheet and the code works.
Question by:Rayne
  • 2

Author Comment

ID: 38334754
So what can be done so that I could use the column values without activating the sheet itself

the three columns have different number of values in them....

Thank you
LVL 51

Expert Comment

by:Martin Liss
ID: 38334771
Do something like

With Worksheets("mySheet").Range("D2")
     Do something with the column values    
End With
LVL 36

Accepted Solution

Norie earned 2000 total points
ID: 38334832
If you don't want the user to see the sheet use 2 Withs, one for the worksheet and one for the range.

Something like this perhaps.
With Worksheets("mySheet")
     With .Range("D2", .Range("D"& Rows.Count).End(xlUp))
        Do something with the column values    
     End With
End With

Open in new window


Author Comment

ID: 38334898
Thank you imnorie :)
Perfection to the ultimate

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question