Selects any range or Visual Basic procedure in any workbook, and activates that workbook if it's not already active.
Reference Optional Variant. The destination. Can be a Range object, a string that contains a cell reference in R1C1-style notation, or a string that contains a Visual Basic procedure name. If this argument is omitted, the destination is the last range you used the Goto method to select.
Scroll Optional Variant. True to scroll through the window so that the upper-left corner of the range appears in the upper-left corner of the window. False to not scroll through the window. The default is False.
This method differs from the Select method in the following ways:
If you specify a range on a sheet that's not on top, Microsoft Excel will switch to that sheet before selecting. (If you use Select with a range on a sheet that's not on top, the range will be selected but the sheet won't be activated).
This method has a Scroll argument that lets you scroll through the destination window.
When you use the Goto method, the previous selection (before the Goto method runs) is added to the array of previous selections (for more information, see the PreviousSelections property). You can use this feature to quickly jump between as many as four selections.
The Select method has a Replace argument; the Goto method doesn't.
This example selects cell A154 on Sheet1 and then scrolls through the worksheet to display the range.
Application.Goto Reference:=Worksheets("Sheet1").Range("A154"), scroll:=True
Finds specific information in a range, and returns a Range object that represents the first cell where that information is found.
Returns Nothing if no match is found. Doesn't affect the selection or the active cell.
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method.
If you don't specify values for these arguments the next time you call the method, the saved values are used.
Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.
To avoid problems, set these arguments explicitly each time you use this method.
You can use the FindNext and FindPrevious methods to repeat the search.
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters "Cour". When Microsoft Excel finds a match, it changes the font to Times New Roman.
Finding any matching values
When searching a large range of cells it is significantly faster to search a column and spin down the rows as opposed to a row and spin through all the columns.
Dim rgeRange As Range
rgeRange = Cells.Find(What:=MyValue)
If Not rgeRange Is Nothing Then
What - The data you want to search for (String)
After - A single cell representing the cell to start searching from
LookIn - The type of data to search.
LookAt - Whether the search is against the whole string or part of the string.
SearchOrder - The order in which to search the range.
SearchDirection - The search direction when searching a range.
MatchCase - True or False to match the case
MatchByte - True to match double byte characters match only
SearchFormat - True to use the Application.FindFormat options.
Find Formatting (new 2002)
Starting in Excel 2002 you can now find and replace cell contents based on the cell formatting.
The formatting takes place via two new CellFormat objects which are addresses through the FindFormat and ReplaceFormat properties.
Finding and selecting the Maximum Value in a Range
Dim WorkRange As Range
If TypeName(Selection) <> "Range" Then Exit Sub
If Selection.Cells.Count = 1 Then
'set it to the whole worksheet
Set WorkRange = ActiveSheet.Cells
Set WorkRange = Selection
MaxVal = Application.WorksheetFunction.Max(WorkRange)
'find it and select it
On Error Resume Next
If Err <> 0 Then MsgBox("Max value was not found: " & MaxVal)
Finding any merged cells
Public Sub BET_Find_MergedCells()
Dim rngCell As Range
Dim stemp As String
stemp = ""
For Each rngCell In Selection
If rngCell.MergeCells = True Then
stemp = stemp & rngCell.Address & " "
Call MsgBox(Left(stemp, Len(stemp) - 1))
Finding Unique Items
This function will return the unique items in an array. Used to find the unique items in a column of Excel data.
Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number of unique elements
' If Count = False, the function returns a variant array of unique elements
Dim Unique() As Variant ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean
' If 2nd argument is missing, assign default value
If IsMissing(Count) Then Count = True
' Counter for number of unique elements
NumUnique = 0
' Loop thru the input array
For Each Element In ArrayIn
FoundMatch = False
' Has item been added yet?
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem '(Exit For-Next loop)
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext