Delete Rows in .xls

[stormlolz]

Hello,

 

I want to clean a .xls file because it very big

I want to delete rows with conditions

 

example :

delete row where columm CV <> "test"

 

how can I do ? 

Thank you  !

 

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <GuiConstants.au3>
#include <Includes\_FileListToArrayEx.au3>
#include <Date.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
#include <GDIPlus.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <FontConstants.au3>
#include <ButtonConstants.au3>
#include <WinAPI.au3>
#include <FileConstants.au3>

GUI1()

Func GUI1()

Global $Largeur = 820, $Hauteur = 720, $Titre = "Clean", $titre1 = "A"
Local $Ver = "-0.01"
Local Const $sFont = "Nyala"

$ButtonConnexion = GUICtrlCreateButton("Connexion", 20, 20, 100, 30)


$Form1 = GUICreate($Titre & $Ver, $Largeur, $Hauteur, -1, -1)


GUISetBkColor("0x8080A0")


GUISetState(@SW_SHOW)

While 1

       $nMsg = GUIGetMsg()
    Switch $nMsg

Case $GUI_EVENT_CLOSE
            Fin()


Case $ButtonConnexion
            $Dbname = FileOpenDialog("", "C:\", "")

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook = _Excel_BookOpen($oExcel, $Dbname)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error opening workbook '" & @ScriptDir & $Dbname & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


Local $sRange = "CV65000:CV65000"
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Deleting cells " & $sRange & ".")
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftUp)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Error deleting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)



Endswitch
Wend
EndFunc

 

 

[water]

Read column "CV" into an array and then loop through this array from the last to the first entry using a counter. If the cell contains "test" then delete row Index + 1 using _Excel_RangeDelete.

[junkew]

    Set ws = Worksheets(1)
    ws.Cells.AutoFilter field:=100, Criteria1:="<>test"
    Set r = ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    r.EntireRow.Delete

above is in vba and the most quickest way without iterating over the rows yourself

• cv = column 100
• and <> test is the filter

 

[Subz]

Something similar to Junkew suggestion:

Local $oWorkbook = _Excel_BookOpen($oExcel, $Dbname)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error opening workbook '" & @ScriptDir & $Dbname & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;~ 100 = Column CV
;~ =test : Filter
_Excel_FilterSet($oWorkbook, Default, Default, 100, '=test')
;~ Offset the range so it doesn't include the first line and delete the filtered rows
$oWorkbook.ActiveSheet.UsedRange.Offset(1,0).Resize($oWorkbook.ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
;~ Remove the filter
_Excel_FilterSet($oWorkbook, Default, Default, 0)

 

[stormlolz]

Thank you i will try it

[stormlolz]

Hello

 

don't work

 

Func DeleteCDP()

Local $aShow[] = ["A, B"]

_Excel_FilterSet($oWorkbook2, "Export OPTIMUS", Default, 184, $aShow)
;~ Offset the range so it doesn't include the first line and delete the filtered rows
$oWorkbook2.Worksheets("Export OPTIMUS").Offset(1,0).Resize($oWorkbook2.Worksheets("Export OPTIMUS").UsedRange.Rows.Count - 1).Rows.Delete
;~ Remove the filter
_Excel_FilterSet($oWorkbook2, "Export OPTIMUS", Default, 0)

EndFunc

can you help me ?

thanks

[Subz]

Can you provide a test spreadsheet?

[water]

I assume you want to filter all rows containing "A" or "B". Then it should be:

Local $aShow[] = ["A", "B"]

_Excel_FilterSet($oWorkbook2, "Export OPTIMUS", Default, 184, $aShow, $xlFilterValues)

 

Edited February 17, 2017 by water

[water]

BTW: "don't work" doesn't help much.

What do you get and what do you expect? Any error messages?

[stormlolz]

No error message

 

there is no filter, i have try with your code, don't work too..

 

i think my sheet is not activate, i have my source sheet on foreground

 

 

 

[water]

It doesn't matter. The above scripts all use COM which doesn't interfere with the GUI.
The only problem I can think of is that while editing a cell by the user a COM script can't access this cell.

What is the value of @error and @extended after you called _Excel_FilterSet?

[stormlolz]

how can i do ?

 

msgbox(0,"",@error)
msgbox(0,"",@extended)
 ?

[water]

Exactly. But combine it into a single statement.

MsgBox(0, "", "@error = " & @error & ", @extended = " & @extended)

 

Edited February 17, 2017 by water

[stormlolz]

@error = 0 

@extended = 0

[Subz]

As mentioned if you can provide an example xls sheet we can test along with your existing code we can then troubleshoot.

[stormlolz]

3 files

 

source file

destination file

autoit code file

 

i want to import source file in destination file [ok]

i want to delete lines with conditions in destination file []

destination.xlsx

source.xls

 

ImportOPTIMUS.au3

Edited February 17, 2017 by stormlolz

[Subz]

Few issues:

a. source.xls had incorrect sheet name "source"
b. Filter was set to =test2 there wasn't any test2 that I could see
c. Syntax was incorrect in the following line:

$oWorkbook2.Worksheets("Export OPTIMUS").UsedRange.Offset(1,0).Resize($oWorkbook2.Worksheets("Export OPTIMUS").UsedRange.Rows.Count - 1).Rows.Delete

Once I fixed those issues, it updated correctly.

[stormlolz]

Hello,

 

Thank you it's running !

 

How can I do to make something else :

Filter all value <>A and <> B ?

 

Local $aShow[] = ["<>A", "<>B"]

_Excel_FilterSet($oWorkbook2, "Export OPTIMUS", Default, 184, $aShow, $xlFilterValues)

 

[Subz]

You mean like this?

_Excel_FilterSet($oWorkbook2, "Export OPTIMUS", Default, 184, "<>A", $xlAnd, "<>B")

 

[stormlolz]

yes thank you so much !!