Copying range to variant

[doubtfire]

I have a range to be copied to variant
Dim r1 As Range
Dim v1 As Variant

Col1 Col2 Col3
A Good Good
B Bad Bad
C Bad Good
D Good Bad
E Good Good

Normally I would
Set r1 = Range("A1:C6")
v1 = r1

Is it possible to copy a filtered range to a variant?
Like the example above to filter "B" out (in this case) when Col2 and Col3 are both equal to "Bad".
Therefore after the v1 will be
Col1 Col2 Col3
A Good Good
C Bad Good
D Good Bad
E Good Good

Any suggestion is welcome.
:helpThanks.:help

[Kenneth Hobs]

Set r1 = Range("A1:C6").SpecialCells(xlCellTypeVisible)

[doubtfire]

Ken,

Thank you again for the rescue.

I have the following
Col1 Col2 Col3
a 1 1
b 0 0
c 0 1
d 1 0
e 1 2

My aim is to eliminate the row "b" since Col2 and Col3 are BOTH zero in value.
The code is followed
Dim r1 As Range
Dim r2 As Range
Dim v As Variant

Set r1 = Range("A1:C6")

r1.AutoFilter field:=2, Criteria1:=">0"
r1.AutoFilter field:=3, Criteria1:=">0"
Set r2 = r1.SpecialCells(xlCellTypeVisible)
v = r2

Two issues here.
1. v DOES NOT yield correct results
2. Can I eliminate ONLY zero on Col2 AND Col3, NOT EITHER.

Other suggestions of above for better solution is welcome.
Thanks.!!!:banghead:

[JKwan]

give this a try and see if this helps:
create a fourth column as a helper column, D2 = "=OR(B2>0,C2>0)", then do a fill down
now, with VBA

Sub test()
Dim r1 As Range
Dim r2 As Range
Dim VisCell As Range

Set r1 = Range("A1:D6")
r1.AutoFilter Field:=4, Criteria1:="TRUE"

Set r2 = r1.SpecialCells(xlCellTypeVisible)
For Each VisCell In r2
MsgBox VisCell.Value
Next
End Sub

[doubtfire]

It does work and fulfill one of my requirements for filtering.
The other is to copy the result to a variant for other purpose.
Thanks.: pray2:

[JKwan]

May not be the cleanest, try this out:
Sub test()
Dim r1 As Range
Dim r2 As Range
Dim VisCell As Range
Dim lRow As Long
Dim lCol As Long

Set r1 = Sheet1.Range("A1:D6")
r1.AutoFilter Field:=4, Criteria1:="TRUE"

Set r2 = r1.SpecialCells(xlCellTypeVisible)
lRow = 1
lCol = 1
For Each VisCell In r2
Sheet2.Cells(lRow, lCol) = VisCell.Value
lCol = lCol + 1
If lCol > 4 Then
lCol = 1
lRow = lRow + 1
End If
Next
End Sub

[doubtfire]

Jkwan,

Your solution does work, but ..
The problem is I have to fix a long code in which the original is to assign a Range to a Variant.
But the issue is the new requirement asks for the original range to be filtered.
Therefore, either I can assign the
1. original range (filtered) to the variant (I have tried but does not work)
Your 2nd idea is to assign a new column to combine the two
conditions is good, and it does work to obtain the filtered range)
2. or to use variant = Union (Range1, Range2, Range3....)
I have tried this
Dim s As String
Dim v As Variant

s = "Range1, Range2, Range3 ..."
v = Union(s)

Guys, any new ideas ...:banghead:

[Kenneth Hobs]

Set r = Union(Range(s))
v = r

[doubtfire]

Ken,



Sub temp()

Dim r As Range
Dim r1 As Range
Dim r3 As Range
Dim r5 As Range
Dim s As String
Dim v As Variant

Set r1 = Range("A1:C1")
Set r3 = Range("A3:C3")
Set r5 = Range("A5:C5")

s = "r1, r2, r3"
Set r = Union(Range(s))
v = r

End Sub


Error is "Argument not optional".

Thanks.:think:

[JKwan]

ok, what about this, is this what you are after with the union?
Sub Test()
Dim r1 As Range
Dim r2 As Range
Dim VisibleRow As Range
Dim v As Range
Dim bInit As Boolean

Set r1 = Sheet1.Range("A1:D6")
r1.AutoFilter Field:=4, Criteria1:="TRUE"
Set r2 = r1.SpecialCells(xlCellTypeVisible)
bInit = True
For Each VisibleRow In r2.Rows
If bInit Then
Set v = VisibleRow
bInit = False
Else
Set v = Union(v, VisibleRow)
End If
Next VisibleRow
End Sub

[doubtfire]

JKwan,

Thank you again, you never say die!:clap:
Another master I learn from.
I have added two NEW lines.
And would like to get response if the result of assignment to the variant is positive.


Sub Test() Dim r1 As Range Dim r2 As Range Dim VisibleRow As Range Dim v As Range
DIM v1 As Variant 'NEW Dim bInit As Boolean Set r1 = Sheet1.Range("A1:D6") r1.AutoFilter Field:=4, Criteria1:="TRUE" Set r2 = r1.SpecialCells(xlCellTypeVisible) bInit = True For Each VisibleRow In r2.Rows If bInit Then Set v = VisibleRow bInit = False Else Set v = Union(v, VisibleRow) End If Next VisibleRow

v1 = v 'NEW End Sub

[JKwan]

Good, glad that helped.

As to Variants, I don't like using them, it has more overheads when dealing with them (also, when using them, you don't have Intellisense), that is just me.

[doubtfire]

JKwan,

Thank you again, you never say die!:clap:
Another master I learn from.
I have added two NEW lines.
And would like to get response if the result of assignment to the variant is positive.


Sub Test() Dim r1 As Range Dim r2 As Range Dim VisibleRow As Range Dim v As Range
DIM v1 As Variant 'NEW Dim bInit As Boolean Set r1 = Sheet1.Range("A1:D6") r1.AutoFilter Field:=4, Criteria1:="TRUE" Set r2 = r1.SpecialCells(xlCellTypeVisible) bInit = True For Each VisibleRow In r2.Rows If bInit Then Set v = VisibleRow bInit = False Else Set v = Union(v, VisibleRow) End If Next VisibleRow

v1 = v 'NEW End Sub