Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Move to next cell down when filtered

  1. #1
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Move to next cell down when filtered

    Hi there,

    I am currently using 'Activecell.Offset(1,0).Select' to move down one cell at a time when I click on a button.

    The problem I have now is that if someone was to filter by something then the 'next cell down' could be hidden behind the filter (by that I mean it didn't meet the filter criteria).

    Is there anyway to move down to the next row, even if that row does not follow on Sequentially.

    Any help would be greatly appreciated.

    Regards,

    James
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  2. #2
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    Maybe:

        Do
            ActiveCell.Offset(1).Select
        Loop While Rows(ActiveCell.Row).Hidden = True

  3. #3
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    Thanks. Now the next problem!!

    When you reach the end of the data (if blank cell) then show a message box and end.

    This is what I had:

    Code:
     
    Private Sub cmb_NEXTLINE_Click()
        If ActiveCell.Offset(1, 0).Value = ""
            MsgBox ("Bottom of file reached")
            Exit Sub
        Else
            ActiveCell.Offset(1, 0).Select
            RUN_FORM
        End If
    End Sub
    Not sure how I would put the DO loop in there!!

    James
    Last edited by JamesW; Sep 22nd, 2010 at 06:00 AM.
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  4. #4
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    Hi James,

    Presuming the sub isn't huge, post the whole thing, and describe what we are wanting to do (in RUN_FORM as well). I doubt that acivating/selecting are necessary.

    Mark

  5. #5
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    That's the whole sub for when you click the button.

    RUN_FORM basically gets lots of cell values and puts them into textboxes on a form.

    If you really want to see the mess that is RUN_FORM then here it is:

    Code:
    Sub RUN_FORM()
        ordernum = ActiveCell.Offset(0, -30).Value
        orderline = ActiveCell.Offset(0, -29).Value
        sku = ActiveCell.Offset(0, -23).Value
        endmarket = ActiveCell.Offset(0, -33).Value
        plant = ActiveCell.Offset(0, -27).Value
        leadtime = ActiveCell.Offset(0, -24).Value
        planprodwk = ActiveCell.Offset(0, -16).Value
        planloaddt = ActiveCell.Offset(0, -13).Value
        planotifdt = ActiveCell.Offset(0, -20).Value
        actprodwk = ActiveCell.Offset(0, -14).Value
        actloaddt = ActiveCell.Offset(0, -11).Value
        eta = ActiveCell.Offset(0, -7).Value
        actarrdt = ActiveCell.Offset(0, -5).Value
        qreq = ActiveCell.Offset(0, -21).Value
        qload = ActiveCell.Offset(0, -9).Value
        qrec = ActiveCell.Offset(0, -4).Value
        otif = ActiveCell.Value
        rcause = ActiveCell.Offset(0, 3).Value
        rcauseinfo = ActiveCell.Offset(0, 4).Value
        servcom = ActiveCell.Offset(0, 5).Value
        plancom = ActiveCell.Offset(0, 6).Value
        movecom = ActiveCell.Offset(0, 7).Value
        SKUDESC = ActiveCell.Offset(0, -22).Value
        servRC = ActiveCell.Offset(0, 8).Value
        planRC = ActiveCell.Offset(0, 9).Value
        moveRC = ActiveCell.Offset(0, 10).Value
        dlLOAD = ActiveCell.Offset(0, 15).Value
        dlARR = ActiveCell.Offset(0, 16).Value
        
        If ActiveCell.Offset(0, 11).Value = "x" Then
            chk_CLOSED.Value = True
        Else
            chk_CLOSED.Value = False
        End If
        
        If ActiveCell.Offset(0, 12).Value = "x" Then
            chk_CANCELLED.Value = True
        Else
            chk_CANCELLED.Value = False
        End If
                
        qeta = qload - qreq
        qact = qrec - qreq
        
        acv = ActiveCell.Address(False, False)
              
        lbl_ac_ORDERNUM = ordernum
        lbl_ac_ORDERLINE = orderline
        lbl_ac_SKU = sku
        lbl_ac_SKUDESC = SKUDESC
        lbl_ac_EM = endmarket
        lbl_ac_PLANT = plant
        lbl_ac_LEADTIME = leadtime
        
        lbl_ac_PPW = planprodwk
        lbl_ac_PLD = planloaddt
        lbl_ac_PORD = planotifdt
        lbl_ac_APW = actprodwk
        lbl_ac_ALD = actloaddt
        lbl_ac_ETA = eta
        lbl_ac_AAD = actarrdt
        lbl_ac_DL_LOAD = dlLOAD
        lbl_ac_DL_ARR = dlARR
        lbl_ac_QREQ = qreq
        lbl_ac_QLOAD = qload
        lbl_ac_QREC = qrec
        lbl_ac_OTIF = otif
        cbo_RC = rcause
        txt_RCI = rcauseinfo
        txt_SERCOM = servcom
        txt_PLANCOM = plancom
        txt_MOVECOM = movecom
        lbl_ac_LINE = "Cell address: " & acv
        cbo_SER_RC = servRC
        cbo_PLAN_RC = planRC
        cbo_MOVE_RC = moveRC
            
        cbo_RC.RowSource = "RootCause"
        
        With cbo_SER_RC
            .AddItem "Customer"
            .AddItem "Materials"
            .AddItem "Move"
            .AddItem "Plan"
            .AddItem "Other"
        End With
        
        With cbo_PLAN_RC
            .AddItem "Customer"
            .AddItem "Materials"
            .AddItem "Move"
            .AddItem "Plan"
            .AddItem "Other"
        End With
        
        With cbo_MOVE_RC
            .AddItem "Customer"
            .AddItem "Materials"
            .AddItem "Move"
            .AddItem "Plan"
            .AddItem "Other"
        End With
        
        UserNameWindows
            
        If noUser = True Then
            cmb_SUBMIT.Enabled = False
        End If
    End Sub
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  6. #6
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,479
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    Try replacing the line "ActiveCell.Offset(1,0).Select" in your macro with the Do loop.

  7. #7
    New Member
    Join Date
    Dec 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move to next cell down when filtered

    Thank you . Code works!

Some videos you may like

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
  •