If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cursor movement to last cell with same number
I have some spreadsheets and several columns within them have only six or
seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. |
#2
|
|||
|
|||
Cursor movement to last cell with same number
Edit/Find, 4097, hold the SHIFT key down and press Find.
"Steven" wrote in message ... I have some spreadsheets and several columns within them have only six or seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. |
#3
|
|||
|
|||
Cursor movement to last cell with same number
I'm sorry, Bob, but I don't follow. I'm using Excel 2007, and can access
Find in two different ways: 1. CTRL-F. I've tried holding down the Shift while typing CTRL-F with no luck. 2. Click on Find and Select from the main page. I've clicked on that, and held down the Shift while clicking on Find, also with no luck. What am I doing wrong? "Bob Umlas" wrote: Edit/Find, 4097, hold the SHIFT key down and press Find. "Steven" wrote in message ... I have some spreadsheets and several columns within them have only six or seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. |
#4
|
|||
|
|||
Cursor movement to last cell with same number
So if your data we
a a a a b b b a a a And you were on the second cell (the second a), you'd want to stop at the 4th cell. If that's the case, you could use a macro. You could assign the macro to a shortcut key if you wanted, but I'd find it easier to rightclick and choose an option. If you want to try... Create a new workbook (or maybe put it in your personal.xls* workbook???). Option Explicit Const myCaption As String = "Find Last In This Group Column" Sub Auto_Open() With Application.CommandBars("Cell") On Error Resume Next .Controls(myCaption).Delete On Error GoTo 0 With .Controls.Add .Caption = myCaption .OnAction = "'" & ThisWorkbook.Name & "'!FindLastInGroup" End With End With End Sub Sub auto_close() With Application.CommandBars("Cell") On Error Resume Next .Controls(myCaption).Delete On Error GoTo 0 End With End Sub Sub FindLastInGroup() If ActiveCell.Value = "" Then 'do nothing Else Application.ScreenUpdating = False Do If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then If ActiveCell.Row = ActiveSheet.Rows.Count Then Exit Do Else ActiveCell.Offset(1, 0).Activate End If Else Exit Do End If Loop Application.ScreenUpdating = True End If End Sub Then save this workbook. Whenever you need this functionality, just reopen this workbook. You may want to add some other checks (no stopping on hidden rows????) -- or anything else you can think of. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Steven wrote: I have some spreadsheets and several columns within them have only six or seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. -- Dave Peterson |
#5
|
|||
|
|||
Cursor movement to last cell with same number
Find and Select from Home Tab
In the Find What dialog box enter 4097 Then hold SHIFT key and hit "Find Next" Gord Dibben MS Excel MVP On Tue, 6 Oct 2009 16:02:03 -0700, Steven wrote: I'm sorry, Bob, but I don't follow. I'm using Excel 2007, and can access Find in two different ways: 1. CTRL-F. I've tried holding down the Shift while typing CTRL-F with no luck. 2. Click on Find and Select from the main page. I've clicked on that, and held down the Shift while clicking on Find, also with no luck. What am I doing wrong? "Bob Umlas" wrote: Edit/Find, 4097, hold the SHIFT key down and press Find. "Steven" wrote in message ... I have some spreadsheets and several columns within them have only six or seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. |
#6
|
|||
|
|||
Cursor movement to last cell with same number
Works like a charm! Thanks, guys!
"Gord Dibben" wrote: Find and Select from Home Tab In the Find What dialog box enter 4097 Then hold SHIFT key and hit "Find Next" Gord Dibben MS Excel MVP On Tue, 6 Oct 2009 16:02:03 -0700, Steven wrote: I'm sorry, Bob, but I don't follow. I'm using Excel 2007, and can access Find in two different ways: 1. CTRL-F. I've tried holding down the Shift while typing CTRL-F with no luck. 2. Click on Find and Select from the main page. I've clicked on that, and held down the Shift while clicking on Find, also with no luck. What am I doing wrong? "Bob Umlas" wrote: Edit/Find, 4097, hold the SHIFT key down and press Find. "Steven" wrote in message ... I have some spreadsheets and several columns within them have only six or seven possible values. Currently I scroll through these columns manually to find the breaks between numbers. Is there a cursor control to move the cursor to the last cell to have the same number as in the cell the cursor currently resides on? For example: 4097 4097 4097 4097 4098 4098 4098 If I'm currently on a 4097 cell, I would want to move down to the last 4097-valued cell. |
Thread Tools | |
Display Modes | |
|
|