Range of Rows filtered by column data

[vagofone]

Hey nice people, I would be really thankful if somebody could help me with this issue:

Is there a way to define a range that consists only of the rows that in some column contain certain data?

Example:

Let's say I have in Column A the categories Cat1 and Cat2
Can I set a range that contains all the rows that have on the column A the text Cat1?

I am sorry I currently have no vba code to post... If I do before i get an answer I am going to post it.

Thanks a lot in advance.

V.

[mancubus]

the point is, what will you do after determining the range? (i dont use "define" here)

copy to another sheet maybe



Sub vbax_56745_filter_range()

With ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=1, Criteria1:="=Cat1"
.AutoFilter.Range.Offset(1).Copy
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.AutoFilterMode = False
End With

End Sub



select it maybe


Sub vbax_56745_union_range_based_on_condition()

Dim LastRow As Long, LastCol As Long
Dim cll As Range, rng As Range

With ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to suit
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

On Error Resume Next

For Each cll In .Range("A1:A" & LastRow)
If cll.Value = "Cat1" Then
If rng Is Nothing Then
Set rng = .Range("A" & cll.Row).Resize(, LastCol)
Else
Set rng = Union(rng, .Range("A" & cll.Row).Resize(, LastCol))
End If
End If
Next cll
End With

If Not rng Is Nothing Then rng.Select

End Sub

[p45cal]

Can I set a range that contains all the rows that have on the column A the text Cat1?
Adapting mancubus' code, myRng is set to such rows:
Sub vbax_56745_filter_range_b()
With ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to suit
.AutoFilterMode = False
.Cells(1).AutoFilter 1, "Cat1"
Set myRng = Intersect(.AutoFilter.Range.Offset(1), .AutoFilter.Range).SpecialCells(xlCellTypeVisible)
.AutoFilterMode = False
End With
myRng.Select 'to demonstrate the set range.
End Sub