Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last value before empty in Column (2002)

    Hello, I wonder if there is a way to do this without macro's.

    Here is the VBA i wrote which does what I want

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intRow As Integer, cell As Range
    If Target.Column = 1 Then
    For Each cell In Range("a1:a6000")
    intRow = cell.Row
    If cell.Value = Empty Then Exit For
    Next
    Range("c1").Value = Cells(intRow - 1, 1).Value
    End If
    End Sub

    but i would like to know if there is a way with Excels functions or validation to do this.

    basically to get the last value in a column and place it elsewhere. I would prefer to use functions as often people will click 'disable macro' when they open a sheet !

    cheers
    Thanks,

    pmatz

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

    Re: Last value before empty in Column (2002)

    Alternatively, for a single formula that handles both text and numbers:

    =INDIRECT(ADDRESS(MAX((A1:A6000<>"")*ROW(A1:A6000) ),1))

    entered as an array formula (confirm with Ctrl+Shift+Enter)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Last value before empty in Column (2002)

    Another good one. I forgot about this one!

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Last value before empty in Column (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 12-Mar-04 10:21. Added some more formulae)</P>Try this(for the last number)
    <pre>=VLOOKUP(9.99999999999999E+307,A1:A6000,1)</pre>


    or this for the last text
    <pre>=VLOOKUP(REPT("Z",200),A1:A6000,1)</pre>


    If your expect blanks "within" your data, this will give the results right before the first blank cell (like your macro will), the previous ones will both ignore blanks. The first will also ignore text, and the 2nd will ignore numbers.

    This is an array (ctrl-shift-enter) to confirm:
    <pre>=INDEX(A1:A6000,MIN(IF(ISBLANK(A1:A6000),ROW( A1:A6000)))-1)</pre>


    This actually will duplicate your macro: the one above will not stop at a null string, like your macro:
    <pre>=INDEX(A1:A6000,MIN(IF(A1:A6000="",ROW(A1:A60 00)))-1)</pre>


    Steve

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last value before empty in Column (2002)

    WOW!

    Thanks chapps, I must now undertake the task of figuring out what and how these functions do what they do... but that's 'fun'!

    [img]/forums/images/smilies/smile.gif[/img]

    Have a good weekend !
    Thanks,

    pmatz

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Last value before empty in Column (2002)

    Many times they will not all give the same results.

    The last 1 I gave should always match your macro.

    The difference (other than the first 2 numbers/text) is a difference between a blank cell and cell with just an apostrophe (') or a formula giving a null string (="")

    Steve

Posting Permissions

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