Jump to content
stormlolz

Delete Rows in .xls

Recommended Posts

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  !

 

#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
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.


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

 

  • Like 1

Share this post


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

 

  • Like 1

Share this post


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

Share this post


Link to post
Share on other sites
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 by water
  • Like 1

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
water

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

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

  • Like 1

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

 

 

 

Share this post


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

  • Like 1

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
stormlolz

how can i do ?

 


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

Share this post


Link to post
Share on other sites
water

Exactly. But combine it into a single statement.

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

 

Edited by water
  • Like 1

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
Subz

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

  • Like 1

Share this post


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

Share this post


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

  • Like 1

Share this post


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

 

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.