Excel VBA find a range of same values in a column

excel, vba

Solution

If you have your first cell in a sorted list, a countif function will give you the last cell easily.

   Function GetFirstCell(CellRef As Range) as long
       Dim l As Long
       l = Application.WorksheetFunction.Match(CellRef.Value, Range("A1:A10000"), 0)
       GetFirstCell = l
   End Function

   function GetLastCell(cellRef as range, lFirstCell as long)
       Dim l As Long
       l = Application.WorksheetFunction.countif(Range("A1:A10000"), CellRef.Value)
       GetLastCell = lFirstCell+l-1
   End Function

Problem

I need to write a macro that will find the cell range based on a value. A column will have the same value in a row, I need to find out what is the first and last column that has the same value in a row. So the macro needs to find that "Jill Cross" range is a4 to a9 So far I don't have much, got a way to find the first occurrence of a value ``` Function GetFirstCell(CellRef As Range) Dim l As Long l = Application.WorksheetFunction.Match(CellRef.Value, Range("A1:A10000"), 0) GetFirstCell = l End Function ``` Now I need to loop through the next rows somehow to return the last row of an occurrence

Original source