Delete Hidden (Unfiltered) Rows in Excel

[Moonscarlet]

Hello,

I am trying to keep only the filtered rows in an excel sheet but couldn't find a fast way to do it for a 1k+ rows file.

Tried going through each row to see if it's hidden or not and if it is, delete it but it's not fast:

Local $oExcel = _Excel_Open()
    Local $File = _Excel_BookOpen($oExcel, $Filepath)
    Local $LastRow = $File.Activesheet.Range("A1000000").End(-4162).Row

    _Excel_FilterSet($File, Default, Default, 1, "Test")
    For $j=1 To $LastRow
        If $File.Activesheet.Range("A"&$j).EntireRow.Hidden Then _Excel_RangeDelete($File.Activesheet,$j&":"&$j)
    Next

I found a VBA macro that works great but I am having a hard time converting it to be able to use it in my script:

Sub RemoveHiddenRows()
    Dim oRow As Range, rng As Range
    Dim myRows As Range
    Set myRows = Intersect(Sheets("Sheet1").Range("A:A").EntireRow, Sheets("Sheet1").UsedRange)

    For Each oRow In myRows.Columns(1).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next

    If Not rng Is Nothing Then rng.EntireRow.Delete
End Sub

I would really appreciate it if anyone can help me so I can use this part in my autoit script.

Thanks.

[water]

https://www.autoitscript.com/wiki/Excel_Filter

describes how to copy all filtered (visible) rows. Should be easy to modify the example to delete all invisible rows 😃

[Moonscarlet]

I don't get it , tried this (for visible) but it's still slow.

For $oRange In $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
    _Excel_RangeDelete($oWorkbook.ActiveSheet,$oRange,Default,1)
Next

Could you please clarify a bit more?

[Subz]

Maybe something like:

_Excel_FilterSet($File, Default, Default, 1, "<>Test")
    $File.ActiveSheet.Usedrange.Offset(1).SpecialCells($xlCellTypeVisible).EntireRow.Delete
    _Excel_FilterSet($File, Default, Default, 1, "=Test")

 

[water]

Set parameter $bScreenUpdating in _Excel_Open to False (and check the remarks in the help file).
This prevents Excel from updating the screen after each line delete.