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

    VBA Max of a column (2000)

    Why doesn't
    iMax = Application.WorksheetFunction.Max(Column(1))
    work?
    Is there a one-liner that does work? (w/o resorting to Evaluate)
    TIA --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>

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: VBA Max of a column (2000)

    Try this two liner
    Columns(1).Select
    imax = Worksheetfunction.Max(Selection)

    Tom Duthie

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

    Re: VBA Max of a column (2000)

    That does not work and it violates
    Sam's Second rule of XL programming:
    2) NEVER use or change the selection

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> <small>But thanks anyway!
    <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>

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

    Re: VBA Max of a column (2000)

    Bizzare! That works! Thanks! I'd like to see an expalnation of why.

    BTW, I resorted to some really weird code instead:
    iMax = [Max(Sheet1!C:C]

    I don't do that very often because there is a lot of overhead; ie, don't do it in a loop. But, for initializing your variables, it's quick & dirty. Dirty, because if you move column C, your code no longer works.
    <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>

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Max of a column (2000)

    <P ID="edit" class=small>(Edited by mbarron on 05-Apr-07 14:26. edit 2 - got rid of extra =)</P>Try this one:

    iMax = Application.WorksheetFunction.Max(Range("A:A"))

    or

    iMax = Application.WorksheetFunction.Max(Cells(1, 2).EntireColumn)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Max of a column (2000)

    You could also use named ranges if you are going to be moving columns.

    imax = Application.WorksheetFunction.Max(Range("sam"))

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Max of a column (2000)

    It should be

    iMax = Application.WorksheetFunction.Max(Columns(1))

    i.e. Columns instead of Column.

    Columns is the collection of columns (in this case of the ActiveSheet since you haven't specified what it belongs to).
    Column returns the column number of a range that contains only one column. Column(1) makes no sense.

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

    Re: VBA Max of a column (2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    <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>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Max of a column (2000)

    You can also just use this if you're a lazy typist like me:
    <code>iMax = Application.Max(Columns(1))</code>
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: VBA Max of a column (2000)

    iMax = Application.Max(Column(1))
    It dosn't work for me! <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    <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>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Max of a column (2000)

    Columns instead of Column again...

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Max of a column (2000)

    iMax = Application.Max(Column<big>s</big>(1))
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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