Finding Cells


Last Column in a Range

Use the following to obtain the last column in a range.

Dim oRange As Range 
Set oRange = Range("A1:E200") 
llastcolumn = oRange(oRange.Rows.Count, oRange.Columns.Count).Column 
Debug.Print llastcolumn

Application.GoTo

Selects any range or Visual Basic procedure in any workbook, and activates that workbook if it's not already active.
expression.Goto(Reference, Scroll)
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

Range.Find Method

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 
   Cells.Find(What:=MyValue _ 
              After:=Range("C2"), _ 
              LookIn:=xlFindLookIn.xlValues, _ 
              LookAt:=xlLookAt.xlPart, _ 
              SearchOrder:=xlSearchOrder.xlByRows, _ 
              SearchDirection:=xlSearchDirection.xlNext, _ 
              MatchCase:=False).Activate 
End If

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

Sub GoToMax 
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 
Else 
   Set WorkRange = Selection 
End If 

MaxVal = Application.WorksheetFunction.Max(WorkRange) 

'find it and select it
On Error Resume Next 
WorkRange.Find(What:=MaxVal, _ 
               After:=WorkRange.Range("A1"), _ 
               LookIn:=xlValues, _ 
               LookAt:=xlLookAt.xlPart, _ 
               SearchOrder:=xlSearchOrder.xlByRows, _ 
               SearchDirection:=xlSearchDirection.xlNext, _ 
               MatchCase:=False).Select 

If Err <> 0 Then MsgBox("Max value was not found: " & MaxVal) 
?? 

End Sub

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 & " " 
      End If 
   Next rngCell 
   Call MsgBox(Left(stemp, Len(stemp) - 1)) 
End Sub

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)  
            End If 
        Next i 
AddItem: 
'       If not in list, add the item to unique list
        If Not FoundMatch Then 
            NumUnique = NumUnique + 1 
            ReDim Preserve Unique(NumUnique) 
            Unique(NumUnique) = Element 
        End If 
    Next Element 
'   Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique 
End Function
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext