Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Instead of 65536 (2000, SR-1)

    Minor point, but in case anybody's interested...
    I've seen a few examples of code posted recently like:
    <pre>ActiveSheet.Range("A65536").End(xlUp).Selec t</pre>

    Going back to when some people were still using Excel 95 (which only had 16,384 rows), I've used the following:
    <pre>With ActiveSheet
    .Cells(.Rows.Count, 1).End(xlUp).Select
    End With</pre>

    Not really a big deal right now, since hardly anybody is still using Excel 95, and Excel 97, 2000 and 2002 (?) all have 65536 rows. If in a future release, though, MS increases the number of rows (I can dream, can't I?), then the code will still work as intended.

    FWIW...

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Instead of 65536 (2000, SR-1)

    Colin,

    A very good point, thank you. It is always a good idea to include flexibility where possible and not hardcode values.

    I'm not sure waht XL2002 has rowwise. It would be nice, given 16777216 cells, to be able to diivide them up as suits between rows and columns. If you just had 20 columns, why not 838,860 rows etc ?

    Andrew

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Instead of 65536 (2000, SR-1)

    Colin

    An excellent point. I have been using this technique a lot, since I do still run some applications, a few, in Excel 5.0c. Your example shows how one should be flexible and not lazy because I also saw some code with:

    ActiveSheet.Range("A65000").End(xlUp).Select. Now this shows laziness to me, and some don't account to the fact that you are missing 536 rows that can make the difference.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instead of 65536 (2000, SR-1)

    Just some comments of my own...

    Generally, any code with .Select in it makes me cringe a little, because there are very few cases where it is desirable to have the code do the selecting for you. Usually, this is a sign that the code started with something that the person recorded. In any of my own code, .Select is a sign of laziness (that is, yours-truly has been lazy).

    For example,
    <pre>ActiveSheet.Range("A65536").End(xlUp).Selec t
    </pre>

    is clearly a "keyboard" way of finding the last used cell in column A. There are other methods for achieving the same result without moving the cursor.

    Generally, I don't bother pointing this out in other people's code unless they are trying to create something that is more "official", will be used or maintained by other people, speed is an issue, etc. If it is a simple routine for doing something simple for a one-time situation, I don't bother bugging people about it if it works.

    These discussions of relatively trivial things can be quite fun!

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Instead of 65536 (2000, SR-1)

    Amen to Jim's words on avoiding/removing Select from macros. Combining the 65536 & Select thoughts, if you want to insert the current date in column A in the first available row of your worksheet, then the macro recorder creates something like:<pre> Range("A87").Select
    ActiveCell.FormulaR1C1 = "11/1/2001"
    </pre>

    which only works one time, if you use it today. Here's how I would change it:<pre> With ActiveSheet.UsedRange
    Cells(.Row + .Rows.Count, 1) = FormatDateTime(Date, vbShortDate)
    End With
    </pre>

    Notice that I avoided the 65536 by adding the first used row (.Row) to the number of used rows (.Rows.Count). I also avoided the Select thus leaving the current worksheet selection alone. Have a great day! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instead of 65536 (2000, SR-1)

    You bring up some interesting comments concerning the use of Select. I am always looking to improve my code writing and do use Select now and then for such things as formatting a range of cells, For..Next statements, ActiveCell.Offset(0,1).Select, etc.

    Do you have some more examples of alternate methods to eliminate the select use?

    TIA

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Instead of 65536 (2000, SR-1)

    > formatting a range of cells...

    Say you want to change the format of A1:A5 to currency. The macro recorder will give you <pre> Range("A1:A5").Select
    Selection.NumberFormat = "$#,##0.00"</pre>

    but you can do this without selecting in one line<pre> Range("A1:A5").NumberFormat = "$#,##0.00"</pre>

    If you give us examples of other code, we can show you how to eliminate the select. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instead of 65536 (2000, SR-1)

    fwiw, there have been discussions of select vs range for a while on these forums- a search in here, VBA or Word should reveal a few.

    I love using the range object when I can, it's so much better. I can't all the time though.

    A big advantage of using the "range" over "select" is that it does not involve screen redraws- so can be vastly more efficient.

    But experiment. Like any rule, there's exceptions.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •