Range / VBA
 

Can you set a range that's conditional on the contents of a cell ?

e.g. where column C contains "Fred" you'd have only the "Fred"s.

And would it include all the other columns in the rows?


Thanks - Kirk

Range / VBA
 

You could try:

Sub test()
Dim rngFound As Range
Dim rngFred As Range
Dim strFirst As String

With Range("C:C")
Set rngFound = .Find(what:="Fred", _
after:=.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
Set rngFred = rngFound
Do
Set rngFound = .FindNext(rngFound)
If rngFound.Address < strFirst Then _
Set rngFred = Union(rngFred, rngFound)
Loop Until rngFound.Address = strFirst
End If
End With

If Not rngFred Is Nothing Then _
Set rngFred = rngFred.EntireRow

MsgBox rngFred.Address

End Sub



"kirkm" wrote:


Can you set a range that's conditional on the contents of a cell ?

e.g. where column C contains "Fred" you'd have only the "Fred"s.

And would it include all the other columns in the rows?


Thanks - Kirk

Range / VBA
 

Many thanks. Given me heaps to work from.

Cheers - Kirk