Most probably you feel like you have a good clue already how to locate a value in a given Excel row. There are plenty of ways – Find() , looking through the cells one by one, looking through the Internet for a solution (I guess this is how you get here).
The picture is from my 6 hour walking tour, quite irrelevant, but beautiful.
Using Range.Find() is ok, if you know what it is doing and how to avoid the common pitfalls. Anyway, somehow I don’t use it usually, but I use my own “stuff” from my own boilerplate – https://github.com/VBoilerplate/Boiler. It is probably not the best and the most optimal solution, writing the range to a C++ array would be slightly faster, but I ain’t got time for that and I am quite ok with any speed less than 1 second. Still, I am using my boilerplate quite a lot, thus I consider it a bit “bulletproof”.
Anyway, some days ago, I have noticed that the function LocateValueCol in my boilerplate, which I am using for since 2015 or so, is actually quite slow in the worse case scenario… Which has not occurred to me at all. This function simply says on which line in Excel is the n-th repeated value of the first column. E.g., if you have a table like the one on the gif, asking for the n-th repetition of “vitoshacademy” will return as follows:
- first is on row 3
- second is on row 5
- third is on row 6
- forth is on row 9
- fifth is not present, e.g., it returns
-999, which is my placeholder for “not found”
And as I have written so far it was working quite ok for quite some time. Anyway, what I did not notice in that code, was that if it returns -999, e.g. if the value was not there, the time to return the value was actually not that quick. Why? Because it was looking through the whole range, up to the end, a bit more than 1 million cells (2^20).
And this is slow, if you have some cases in your code, in which it has to inform you that the n-th value is not there (as I said, this is not C++ array). Anyway, I have rewritten it, simply checking to the last cell with data, which usually is before 2^20. And it works quite ok, if you add all the functions from the boilerplate:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Public Function LocateValueRow(ByVal textTarget As String, _ ByRef wksTarget As Worksheet, _ Optional col As Long = 1, _ Optional moreValuesFound As Long = 1, _ Optional lookForPart = False, _ Optional lookUpToBottom = True) As Long Dim valuesFound As Long Dim localRange As Range Dim myCell As Range Dim lastRowOnColumn1 As Long LocateValueRow = -999 valuesFound = moreValuesFound lastRowOnColumn1 = LastRow(wksTarget.Name) Set localRange = wksTarget.Range(wksTarget.Cells(1, col), wksTarget.Cells(lastRowOnColumn1, col)) For Each myCell In localRange If lookForPart Then If UCase(textTarget) = UCase(Left(myCell, Len(textTarget))) Then If valuesFound = 1 Then LocateValueRow = myCell.Row If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If Else If UCase(textTarget) = UCase(Trim(myCell)) Then If valuesFound = 1 Then LocateValueRow = myCell.Row If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If End If Next myCell End Function |
What is the point of writing this article? Well, it is not boasting, that I can find the last row in Excel quite quickly using my boilerplate. The main reasons are actually two:
- Showing that it does not matter how much time you are using some code, it can always be improved significantly with the time;
- It is really tough for 1 person to create a VBA-Excel boilerplate with error prone code. It is more like a team work. Thus, feel invited to the project and if you have any ideas how to improve it, feel free to make a PR. I will review it and most probably I will include it;
The Excel-VBA Boilerplate project in GitHub.
Thank you for your attention.