xlUp Help Please
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: xlUp Help Please

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have the following code I am trying to use this to find the last value grater than zero. This code finds zero values how can I get it to ignor zero values

    LastB = Range("B65536").End(xlUp).Row
    LastOfAll = LastB
    Range("I" & LastOfAll).Select

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi nehpets! How about using AutoFilter method?

    With Columns("B:B")
    .AutoFilter field:=1, Criteria1:="<>0"
    Range("B65536").End(xlUp).Offset(0, 7).Select
    .AutoFilter
    End With

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to do this on selected columns only to set up a print area This is the code I am using at the moment but it dosnt ignor zero values



    Range("B65536").Offset(0, a).Select
    lastb = Range("B65536").End(xlUp).Row
    LastC = Range("C65536").End(xlUp).Row
    LastG = Range("G65536").End(xlUp).Row
    LastH = Range("H65536").End(xlUp).Row
    LastI = Range("I65536").End(xlUp).Row


    LastOfAll = lastb

    If LastC > LastOfAll Then
    LastOfAll = LastC
    End If
    If LastG > LastOfAll Then
    LastOfAll = LastG
    End If
    If LastH > LastOfAll Then
    LastOfAll = LastH
    End If
    If LastI > LastOfAll Then
    LastOfAll = LastI
    End If

    Range("I" & LastOfAll).Select
    '
    End Sub

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hello again, Pls try this.


    Sub test()
    Dim arr, arrERow, i As Integer
    Application.ScreenUpdating = False
    arr = Array("B", "C", "G", "H", "I")
    ReDim arrERow(LBound(arr) To UBound(arr))
    For i = LBound(arr) To UBound(arr)
    With Columns(arr(i))
    .AutoFilter field:=1, Criteria1:="<>0"
    arrERow(i) = Cells(65536, arr(i)).End(xlUp).Row
    .AutoFilter
    End With
    Next
    Application.ScreenUpdating = True
    Range("I" & Application.WorksheetFunction.Max(arrERow)).Select
    End Sub


    regards,
    Colo

    [ This Message was edited by: Colo on 2002-04-16 02:53 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    FANTASTIC thank you very much for all your help it worked

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com