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

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	List	FATHER'S MOBILE	CENTRE	MOTHER'S MOBILE	ADDRESS2	Rand01	Rand02	Rand03	Mobiles
2​	1​	999​	Q	123​	ADDE1	F01	G01	H01	2​
3​	2​	888​	W	234​	ADDE2	F02	G02	H02	2​
4​	3​	777​	E		ADDE3	F03	G03	H03	1​
5​	4​		R	345​	ADDE4	F04	G04	H04	1​
6​	5​		A		ADDE5	F05	G05	H05	0​
7​	6​		B		ADDE6	F06	G06	H06	0​
8​	7​		C	456​	ADDE7	F07	G07	H07	1​
9​	8​	333​	T	567​	ADDE8	F08	G08	H08	2​
10​	9​	222​	Y		ADDE9	F09	G09	H09	1​
11​	10​	111​	U	678​	ADDE10	F10	G10	H10	2​

Sheet: Master