Public Function BigRange(Startrange As Range, Criteria As String) As Range
Dim Fst As Range
Dim FoundAll As Range
Dim F As Range
Set Fst = Startrange.Find(Criteria)
Set FoundAll = Fst
Set F = Fst
If Not F Is Nothing Then
Do
Set F = Startrange.FindNext(F)
If F.Address <> Fst.Address Then
Set FoundAll = Union(FoundAll, F.offset(0,1))
End If
Loop While F.Address <> Fst.Address
End If
Set BigRange = FoundAll
End Function
So in your case you would call it like:
Code:
dim myRange as range
set myRange = bigrange(worksheets("Sheetname").range("A"), "Test")
Thank you for your help. I really appreciate this.
I tested your function. Following are the results.
The function call,
Code:
set myRange = bigrange(worksheets("Sheetname").range("A"), "Test")
caused a run-time error 1004.
Since the function is asking for Startrange, I assume it needs to know the first cell in the range, so after editing it to read .range("A2"), it passed muster.
Then, stepping through the function itself, I saw that you were setting the criteria for the Find, then looping through Startrange with FindNext. The problem is that Startrange contained just one cell.
I then edited the function call to include all of the column I was interested in:
Code:
Set myRange = BigRange(Worksheets("Sheet1").Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)), "Test")
This seemed to satisfy the needs of the function.
The next bit I don't quite understand, as I'm an Access developer and do not have great experience in Excel VBA.
That said, I see that for each cell address in the supplied range, you're creating a new range consisting of the union of that new range and the next cell below.
If I understand that bit correctly, then I feel I shouldn't be passing to the function a column or range of cells, but a range consisting of just one cell.
If I do that, 'F.Address <> Fst.Address' is never True and I end up with the function returning a range containing just one cell.
If I pass to the function a range consisting of all the cells I want to test against for criteria, it returns a range that contains:
1. All but one of the cells in Column B which match the criteria
2. One of the cells from Column A
I misread the Offset function. Told ya I'm not used to this
So the loop adds the cell to the right, which is actually what I need.
The resulting range, however, does include one cell from the criteria column (in a nutshell)
i was expecting range("A") to be the whole of column A but never mind you have got past that. When I wrote the function I wasn't using the offset and when I converted it I forgot about the first result. Change the following line and you should be sorted.
Code:
' change: Set FoundAll = Fst to
Set FoundAll = Fst.offset(0,1)
Yes, I'm extending your function (I've actually added a second column to be tested for the criteria) and was just testing precisely the edit you've posted here. The results are what I need.
Truthfully, though, I am attempting to do a bit more. I'm taking this a step at a time so as not to overcomplicate my original post.
Now that I've got the range of cells which match my criteria, how may I inspect that range for duplicates and pass the 'filtered' range out of the function?
What I'm trying to achieve is this:
The final range I really need is one which matches my criteria and is then is inspected for duplicates. The range, ideally, will contain only those duplicated cells.
The goal is to apply some conditional formatting on those cells in the new range.
I have seen quite a few subroutines which deal with duplication on the web. Too many - I got a bit overwhelmed with all that information.
