Move to next cell down when filtered

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
Maybe:

<font face=Courier New>    <SPAN style="color:#00007F">Do</SPAN><br>        ActiveCell.Offset(1).Select<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Rows(ActiveCell.Row).Hidden = <SPAN style="color:#00007F">True</SPAN></FONT>
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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:

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
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
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,737
Try replacing the line "ActiveCell.Offset(1,0).Select" in your macro with the Do loop.
 

Forum statistics

Threads
1,181,649
Messages
5,931,214
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top