Excel Filter with Powershell

I was trying filter option in Microsoft Excel via powerShell.

According to the requirement, I need to filter out all the values which are non-blank from 2 of the columns.
For example:
There are 10 rows in 2 of the columns of a workbook. Out of which 3 rows are blanks and 7 are non-blanks in each column. My requirement is to get those 7 rows, eliminating the blanks.

The code developed so far, only gives me the exact filter value i.e blanks, but I need the all the non blanks from 2 columns.

$oldfile = “xyz.xlsx” # source’s fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don’t prompt the user
$wb1 = $xl.workbooks.open($oldfile) # open target
$sh1 = $wb1.sheets.item(‘Sheet1’) # sheet in workbook
$xl.Visible = $true
#Column 1
$xlFilterValues = “” # This gives me all the blanks of the column

#Column 2
$xlFilterValues1 = “Class” # This gives me all the blanks of the column


$copyrange = $sh1.Range(“A1:V1”).EntireColumn
$copyrange.copy() | Out-Null
$wb1.close($true) # close and save workbook
$xl.quit() #>


Why are you using Excel COM object? This might be unstable since it’s basically spawning an Excel instance in the background. I would use OleDB instead and you can filter what you want using standard SQL syntax.