Excel Quick Question: End With

Posted on 2012-08-26
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

    Author Comment

    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 44

    Expert Comment

    by:Martin Liss
    Do something like

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

    Accepted Solution

    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

    Thank you imnorie :)
    Perfection to the ultimate

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now