Excel Auto Filter

[Jzullo]

Guys, I need your help once again. Is there any UDF or other resource that I can use to use the Auto Filter function from Excel? I'm trying to filter a list and show whatever's blank. Once the list is filtered, I want to delete the rows.

Thanks in advance.

[evilertoaster]

The most popular excel UDF I know of is - http://www.autoitscript.com/forum/index.php?showtopic=34302

But it seems to not have that method implamented.

Here's the MSDN doc on it though- http://msdn2.microsoft.com/en-us/library/m...ter(VS.80).aspx

so you should be able to stub it into the excel library pretty easily

[Jzullo]

Thanks, Evilertoaster. I'll try to implement that method using the information you directed me to.

[Ármányos Kő]

Thanks, Evilertoaster. I'll try to implement that method using the information you directed me to.

I just did this:

Do
        $empty = ""           ; just to make sure as it created error once...
        $empty = _ExcelReadCell($Status,$i, 3)
        If $empty <> "" Then
            $i =  $i+1
        Else
            $Status.ActiveSheet.Rows($i).Delete
            $deleted = $deleted + 1
        EndIf
    Until $i = $listlength - $deleted

Checks if anything is in row 3 and deletes that row if not...

You have to sort out values beforehand ($listlength, ...) and make your $oExcel too ($Status).

It's sort of recursive.

Edited May 17, 2009 by Ármányos Kő

[Atavis]

I know this is an old topic, but it's the first result in a Google search so it's probably still useful to throw in my two cents.

 

This is my first post here, so i'm not real familiar with how to embed code, etc. - apologies in advance.

 

---- 

This is what i came up with to solve the problem.   You can just loop through blank rows as suggested above, but i was doing several thousands rows on several hundred sheets, so it was not an option.

*Below is assuming any row containing data would have data in column A.  This needs to be modified if your data can have a blank value in column A, but still be a valid row you want to keep*

local $SheetName = "Sheet1"

local $total = $oExcel.Worksheets($SheetName).UsedRange.Columns("A:A").Cells.Count
$oExcel.Columns("A:A").Select  
$oExcel.Selection.Autofilter("1",'=') 
$oExcel.Rows("2:" & $total).Delete  ; change 2: to 1: if you dont want to keep the first row (headers)