Why this isn’t already a function of Excel baffles me. Perhaps my work is somewhat unique. I generally work with very large datasets (over 100,000 rows, often times nearing the 1.08 million row limit of Excel 2007), and I often find myself needing to scroll through the data in order to find the next value in a series.
Consider a spreadsheet with three columns: Fruit, Name, and Score. Imagine this is populated with the data of a survey of 100,000 people and their opinions of Apples, Bananas, and Oranges. You can assume that with three fruit types, there would be 300,000 total rows. Now imagine opening this file in Excel, and imagine it is already sorted by Fruit. How would you go about locating the first entry of Bananas?
The obvious method is to simply use the scroll bar and drag down until Bananas appear. A second method would be to press and hold Page Down until this section appears. In either case, it is far too easy to surpass the intended row, requiring a similar method in the opposite direction. This can sometimes force you to go back and forth a few times until homing in on that row.
Now imagine that you just want to access the last row in the data, regardless of the contents of the cells. Just type Ctrl + Down Arrow, and you’re there. Wouldn’t it be nice if there were a similar function for skipping down to the next unique value? Well, there can be!
Consider the following VBA functions:
Sub findFirst() Dim targetString as String targetString = ActiveCell.Text If ActiveCell.row = 1 Then Exit Sub ElseIf Cells(ActiveCell.row - 1, ActiveCell.Column).Text <> targetString Then Cells(ActiveCell.row - 1, ActiveCell.Column).Select Else ActiveSheet.Columns(ActiveCell.Column).Find(targetString, LookIn:=xlValues).Select End If End Sub Sub findLast() Dim targetString As String targetString = ActiveCell.Text If Cells(ActiveCell.row + 1, ActiveCell.Column).Text <> targetString Then Cells(ActiveCell.row + 1, ActiveCell.Column).Select Else ActiveSheet.Columns(ActiveCell.Column).Find(targetString, SearchDirection:=xlPrevious, LookIn:=xlValues).Select End If End Sub
In both instances, we’re using the built-in function Excel uses to find values, similar to using Find or Find/Replace. In the first function, we first check to make sure we’re not already at the first row, and if not, we use the find function to locate the first instance of the current value in the current column. So, using our original example, if we were midway through the entries of Apples and wanted to return to the first Apple entry, this function would look at the Fruit column and search for the first instance of “Apple”.
The second function does a similar act, but uses the xlPrevious search direction. Therefore, it starts at the first cell in the current column, and searches backwards for the current value. This requires Excel to start from the bottom of the spreadsheet and search upwards. So, if we are halfway through the “Apple” entries and use this function, Excel will start at cell A1048576 and search upward until reaching cell A200001, which would be the last hypothetical instance of “Apple”.
In either function, if the cell directly above or below (respectively) the active cell has a different value than the current value, that cell is simply selected.