stormlolz

Delete Rows in .xls

22 posts in this topic

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  !

 

#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

 

 

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
    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

 

1 person likes this

Share this post


Link to post
Share on other sites

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)

 

1 person likes this

Share this post


Link to post
Share on other sites

Thank you i will try it

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Can you provide a test spreadsheet?

1 person likes this

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

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

 

 

 

Share this post


Link to post
Share on other sites

#11 ·  Posted

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?

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted

how can i do ?

 


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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Exactly. But combine it into a single statement.

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

 

Edited by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#14 ·  Posted

@error = 0 

@extended = 0

Share this post


Link to post
Share on other sites

#15 ·  Posted

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

1 person likes this

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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 by stormlolz

Share this post


Link to post
Share on other sites

#17 ·  Posted

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.

1 person likes this

Share this post


Link to post
Share on other sites

#18 ·  Posted

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)

 

Share this post


Link to post
Share on other sites

#19 ·  Posted

You mean like this?

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

 

1 person likes this

Share this post


Link to post
Share on other sites

#20 ·  Posted

yes thank you so much !!

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