Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel

ResolvedVersion 365

Excel

I have a spreadsheet with over 1000 rows. It is separated by month and some cases names will come up more than once throughout the year. If I do Ctrl+F and search for the name I'll find the first entry. If I click Find All and then scroll down I can find the last entry. Is there any way of going to the last entry first as that is the entry I'll need to update and going there first will reduce the steps by two each time which will speed up my activity significantly.

RE: Excel

Hi George,

Thank you for the forum question.

I can only come up with a VBA solution. If you copy the code below and paste it in a module in the file you when you execute the code will get a input box. Type what you will like to find and the code will select the last instance.

Sub FindLastInstance()
Dim str As String
Dim NumRow As Long
Dim NumCol As Integer
Dim RowCount As Long
Dim ColCount As Integer

str = InputBox("Enter search text")

NumRow = ActiveSheet.UsedRange.Rows.Count
NumCol = ActiveSheet.UsedRange.Columns.Count

For RowCount = NumRow To 1 Step -1
For ColCount = NumCol To 1 Step -1
If Cells(RowCount, ColCount) = str Then
Cells(RowCount, ColCount).Select
Exit Sub
End If

Next ColCount
Next RowCount


End Sub





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 24 Sep 2021: Automatically marked as resolved.

Excel tip:

How to select certain data in an Excel 2010 workbook

If you want to select the correct data set in a page full of data, the most accurate and efficient way of doing this is to use the ''Shift and Click'' technique.

For example: If you want to select all data in cells A2 to E10, then click on cell A2, hold down the Shift key and click on cell E10 and all the data you want to see is highlighted.

Keep holding down the Shift key and you can move from cell E10 to any other cell in the spreadsheet.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.