VBA: Filter before union

[dpatel20]

Hi, I am creating a union for later use (export to CSV). Is there a way to filter the columns before the union WITHOUT copying and pasting to a new sheet.

So...

    With sh.ListObjects("MasterData")
        Set rngToSave = Application.Union(.ListColumns("FATHER'S MOBILE").DataBodyRange, _

                                            .ListColumns("CENTRE").DataBodyRange, _
                                            .ListColumns("MOTHER@S MOBILE").DataBodyRange, _
                                            .ListColumns("ADDRESS2").DataBodyRange)
    End With

In the above, can I filter out rows where both 'FATHER'S MOBILE' and 'MOTHER'S MOBILE' are blank?

 

[Yongle]

One way

Add a column to your table and filter on that column (in my test it was the 9th column in the table)
- column returns
0 (NEITHER parent has a mobile
1 (ONE parent has a mobile)
2 (BOTH parents have a mobile)

Formula is
=COUNTA([@[FATHER''S MOBILE]],[@[MOTHER''S MOBILE]])

    [I][COLOR=#ff0000]On Error Resume Next [/COLOR][/I]                           'required to prevent code crashing
   [COLOR=#ff0000][I] sh.ShowAllData    [/I][/COLOR]                              'clear prior filter
    With sh.ListObjects("MasterData")
[I][COLOR=#ff0000]        .Range.AutoFilter Field:=[/COLOR][COLOR=#0000cd][B]9[/B][/COLOR][COLOR=#ff0000], Criteria1:="<>0", Operator:=xlAnd[/COLOR][/I]
        Set rngToSave = Application.Union(.ListColumns("FATHER'S MOBILE").DataBodyRange, _
            .ListColumns("CENTRE").DataBodyRange, _
            .ListColumns("MOTHER'S MOBILE").DataBodyRange, _
            .ListColumns("ADDRESS2").DataBodyRange)[I][COLOR=#ff0000].SpecialCells(xlCellTypeVisible)[/COLOR][/I]
    End With
      
    MsgBox rngToSave.Address(0, 0)                  'remove after testing
 [COLOR=#ff0000][I]   sh.ShowAllData[/I][/COLOR]
    [COLOR=#ff0000]On Error GoTo [/COLOR][COLOR=#ff0000]0[/COLOR]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]List
[/td][td=bgcolor:#4472C4]FATHER'S MOBILE
[/td][td=bgcolor:#4472C4]CENTRE
[/td][td=bgcolor:#4472C4]MOTHER'S MOBILE
[/td][td=bgcolor:#4472C4]ADDRESS2
[/td][td=bgcolor:#4472C4]Rand01
[/td][td=bgcolor:#4472C4]Rand02
[/td][td=bgcolor:#4472C4]Rand03
[/td][td=bgcolor:#4472C4]Mobiles
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]

1​

[/td][td=bgcolor:#D9E1F2]

999​

[/td][td=bgcolor:#D9E1F2]Q[/td][td=bgcolor:#D9E1F2]

123​

[/td][td=bgcolor:#D9E1F2]ADDE1[/td][td=bgcolor:#D9E1F2]F01[/td][td=bgcolor:#D9E1F2]G01[/td][td=bgcolor:#D9E1F2]H01[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2​

[/td][td]

888​

[/td][td]W[/td][td]

234​

[/td][td]ADDE2[/td][td]F02[/td][td]G02[/td][td]H02[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]

3​

[/td][td=bgcolor:#D9E1F2]

777​

[/td][td=bgcolor:#D9E1F2]E[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]ADDE3[/td][td=bgcolor:#D9E1F2]F03[/td][td=bgcolor:#D9E1F2]G03[/td][td=bgcolor:#D9E1F2]H03[/td][td=bgcolor:#D9E1F2]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

4​

[/td][td][/td][td]R[/td][td]

345​

[/td][td]ADDE4[/td][td]F04[/td][td]G04[/td][td]H04[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#D9E1F2]

5​

[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]A[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]ADDE5[/td][td=bgcolor:#D9E1F2]F05[/td][td=bgcolor:#D9E1F2]G05[/td][td=bgcolor:#D9E1F2]H05[/td][td=bgcolor:#D9E1F2]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

6​

[/td][td][/td][td]B[/td][td][/td][td]ADDE6[/td][td]F06[/td][td]G06[/td][td]H06[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td=bgcolor:#D9E1F2]

7​

[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]C[/td][td=bgcolor:#D9E1F2]

456​

[/td][td=bgcolor:#D9E1F2]ADDE7[/td][td=bgcolor:#D9E1F2]F07[/td][td=bgcolor:#D9E1F2]G07[/td][td=bgcolor:#D9E1F2]H07[/td][td=bgcolor:#D9E1F2]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

8​

[/td][td]

333​

[/td][td]T[/td][td]

567​

[/td][td]ADDE8[/td][td]F08[/td][td]G08[/td][td]H08[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td=bgcolor:#D9E1F2]

9​

[/td][td=bgcolor:#D9E1F2]

222​

[/td][td=bgcolor:#D9E1F2]Y[/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]ADDE9[/td][td=bgcolor:#D9E1F2]F09[/td][td=bgcolor:#D9E1F2]G09[/td][td=bgcolor:#D9E1F2]H09[/td][td=bgcolor:#D9E1F2]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

10​

[/td][td]

111​

[/td][td]U[/td][td]

678​

[/td][td]ADDE10[/td][td]F10[/td][td]G10[/td][td]H10[/td][td]

2​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Master[/td][/tr][/table]