Sign in to follow this  
Followers 0
Jzullo

Excel Auto Filter

5 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Ármányos Kő

Share this post


Link to post
Share on other sites

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) 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0