<Noobie>Excel first script

[zsoltm]

Hello everybody. I working in office and I need to do a lot of repetitive excel job ... thats how I get here lol. So I am very noobie yet. Thats why i want to ask your help if not problem. I am kinda understand the GUI...that is not a problem... My problem is starting when I need to write a script (its ironic, isn't it?)

So  the first script should do the followings :

1. Insert a New Column (should be B but doesnt really matter)
2. add a formula to the whole Column (Ctrl + Enter)
3. If the result is Yes DELETE the whole row which are yes (I using Kutools for Excel when i doing manually by hand)
4. after clear the coumn
5. add a second formula
6. if yes delete the whole rows again.
7. after delete the whole column.

I'm not expect to write me this ( but if you do that is very appriciable) so if you can show me a similar script that would be help a lot.

That is what I had done so far:
 

expandcollapse popup

#include <GUIConstants.au3>
#include <Constants.au3>



; GUI Creation
GUICreate("Excel LEAD CleanUP", 150, 320)
GUISetIcon(@WindowsDir & "\explorer.exe", 0)



; Menu Creation
$filemenu = GUICtrlCreateMenu("File")
GUICtrlCreateMenuItem("Start", $filemenu)
GUICtrlCreateMenuItem("Stop", $filemenu)
GUICtrlCreateMenuItem("Exit", $filemenu)
; Context Menu
$contextmenu = GUICtrlCreateContextMenu()
; Context Menu Item
GUICtrlCreateMenuItem("Example1", $contextmenu)
GUICtrlCreateMenuItem("", $contextmenu) ; separator
GUICtrlCreateMenuItem("Example2", $contextmenu)
; BusinessRemoval
$business = GUICtrlCreateButton("BusinessRemoval", 15, 10, 120, 25)
; Address Lane 1 Separator
$address1 = GUICtrlCreateButton("Clean Address1", 15, 40, 120, 25)
; Address Lane 2 Separator
$address2 = GUICtrlCreateButton("Clean Address2", 15, 70, 120, 25)
; Postcode Cleaner
$postcode = GUICtrlCreateButton("Postcode", 15, 100, 120, 25)
; Phone number separator
$phone = GUICtrlCreateButton("Phone Cleaner", 15, 130, 120, 25)
; Phone PREFIX
$phone = GUICtrlCreateButton("PREFIX", 15, 160, 120, 25)
; Progress Bar
GUICtrlCreateLabel("Progress Example", 15, 260, 120, 25)
$progress = GUICtrlCreateProgress(15, 260, 120, 25)
GUICtrlSetData($progress, 33)
; Dummy
$dummybutton = GUICtrlCreateButton("Dummy Example", 15, 230, 120, 25)
$dummy = GUICtrlCreateDummy()
; Display GUI
GUISetState(@SW_SHOW)
; Continuous Loop to check for GUI Events
While 1
    $guimsg = GUIGetMsg()
    Select
        Case $guimsg = $dummybutton
            GUICtrlSendToDummy($dummy)
        Case $guimsg = $dummy
            MsgBox(0, "Dummy Example", "You have clicked the dummy button.")
        Case $guimsg = $GUI_EVENT_CLOSE
            Exit
    EndSelect
WEnd

just copied from the quick guide.

Thanks a lot. 

[water]

Welcome to AutoIt and the forum!

AutoIt comes with a lot of UDFs (User Defined Functions). One of them handles Excel. It is a bit limited to XLS files and and is a bit slow.

If you want to work with the latest beta version of AutoIt I suggest my rewrite of the Excel UDF (for download please see my signature). My UDF supports all what you want to do.

Edited October 13, 2013 by water

[zsoltm]

Hello water! thank you I already downloaded. 
when i including the Excel rewrite its giving me an error.

C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(168,56) : ERROR: ObjGet() [built-in] called with wrong number of args.

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(249,57) : ERROR: ObjGet() [built-in] called with wrong number of args.

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iIndex + 1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(1155,27) : ERROR: _ArrayTranspose(): undefined function.

_ArrayTranspose($vResult)

~~~~~~~~~~~~~~~~~~~~~~~~^

C:UserssainsburysDocumentsscript.au3 - 3 error(s), 0 warning(s)

!>21:47:35 AU3Check ended. Press F4 to jump to next error.rc:2

>Exit code: 2    Time: 0.425

or what i should include? 

[water]

My UDF needs to be run with the latest beta version of AutoIt

[zsoltm]

yep alright I downloaded the beta and if execute the Beta Run than its alright. anything else what can make my life easier?

[zsoltm]

I recorded a macro in excel but after gave me an error so I just copied what i can use. Its very raw I dont even now what do I need to change to be acceptable for autoIT.. I am asking for help becouse the time just working against me. 

expandcollapse popup

Columns("A:A").Select
Selection.Insert Shift : = xlToRight, CopyOrigin : = xlFormatFromLeftOrAbove
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"quality","st","rainbow","carryout","auto","surgery","public","fertiliser","Fertilisers","ifertilisersl","rendezvous","remember","that","precast","barefoot","mail","scaffolding","safe","access","salvation","army","funeral","directors","director","seahorse","simply","affordable","stich","time","dressmaking","alterations","hospital","by","jewellers","post","sweet","sensation","trophies","engraving","protection","produce","public","relations","made","fun","auctions","auction","kiltmakers","kiltmaker","linen","chippy","timber","tidy","tangles","past","dealer","trading","crafts","craft","naval","activities","US","Uncle","Diner","Removals","Volunteer","Zoo","Wavelength","Wavelengths","Friends","hairdressing","theatreclub","daynursery","MUSLC","superstore","colleges","AALCO","AARDVARK","ABACUS","ABB","ABBOTSWELL","ABBOTTPUBLIC","ABC","ABCON","ABDN","ABERCARE","ABERDEE","ABERDEEN","ABERDEENSHIRE","ABERFLORA","ABERGELDIE","ABERGLEN","ABERLOUR","ABERMED","ABERNE","ABERPEST","ABERSOL","ABERT","ABSCOT","ABSOFT","ABSPEK","ABSURE","ABTEX","ABTRUST","ACADEMY","ACCESS","ACCESSORIES","ACCOMMODATION","ACCORD","ACCOUNTANCY","ACCURAY","ACE","ACTION","ADAPT","ADEPT","ADMIN","ADMIRAL","ADS","ADULT","ADVANCE","ADVANCED","ADVANT","ADVANTAGE","ADVICE","AEA","AFFAIR","AFOS","AGA","AGE","AGENCY","AGIP","AGRI","AGRICULTURAL","AGRICULTURE","AHT","AI","AIDS","AIR","AIRBORNE","AIRFAIR","AIRLINES","AIRNAUTIC","AIRPAC","AIRPORT","AIRWAYS","AIRYHALL","AIRYLEA","AKRON","ALAD","ALASDAIR","ALB","ALBATECH","ALFA","ALL","ALLIANCE","ALLIED","ALLOMAX","ALPINE","ALSTEV","ALTERNATIVES","ALTRA","ALWAYS","ALZHEIMER","AM","AMALGAMATED","AMAT","AMATOLA","AMBER","AMEC","AMERICAN","AMETEK","AMIN","AMITEC","AMOCO","AMTECH","AMTRAK","AMUSEMENT","AND","ANDERGAUGE","ANIXTER","ANKA","ANTIQUES","ANTRIM","AOC","AP","APARDION","APEX","APOLLO","APOSTOLIC","APPEALS","APPLIED","APPOLLO","AQUA","AQUARISTS","AQUATEC","AQUATIC","AQUIDATA","ARCHITECTURAL","ARCO","ARGONAUT","ARGOSY","ARISTACUT","ARJO","ARJON","ARKAIG","ARTISTES","ARTISTS","ARTWORKS","ARUP","AS","ASA","ASCOT","ASDASUPERSTORES","ASHVALE","ASPECT","ASSEMBLIES","ASSESSORS","ASSOCIATED","ASSOCIATES","ASSOCIATION","ASSURANCE","ASTER","ASTRA","ATEL","ATHENA","ATHENAEUM","ATHOLL","ATLANTIC","AUCHMILL","AUCHTERLESS","AUGHTON","AUGUST","AUP","AUQUHARNEY","AURORA","AUTO","AUTOFIX","AUTOGLASS","AUTOMAGIC","AUTOQUICK","AUTOSAVE","AUTOSPRAY","AUTOTRUCK","AVCO","AVENUE","AWAKENING","AWAY","AXIOM","AZTEC","BAADER","BALNAGASK","BANKHEAD","BAR","BARICO","BARRIER","BARTERING","BASELINE","BATCHBRIGHT","BATH","BATHROOM","BAVARIA","BAYLISS","BAYLOR","BEARING","BEATTIES","BEAUTY","BEAVERBROOKS","BEECHWOOD","BEEFEATER","BEELINE","BEFRIEND","BENBOW","BESTWAY","BEWS","BIKES","BLIND","BLINDCRAFT","BLINDS","BLOOD","BLOOMING","BLOSSOMS","BLOW","BLUEBELLS","BMT","BNA","BOARD","BOARS","BOAT","BODY","BOHEMIA","BOILERMAKERS","BON","BONADDIO","BONAVENTURE","BONIVIEW","BONNYMUIR","BOOKBINDING","BOOTS","BOROWSKI","BORSALINO","BOSANQUET","BOSCH","BOTTLED","BOULEVARD","BOURTREE","BOWEN","BOWLING","BOWTECH","BOX","BP","BPCC","BRA","BRACKENRIDGE","BRAEHEAD","BRAESIDE","BRAKE","BRIEF","BRITANNIA","BRITANNIC","BROAD","BROADCAST","BROADSTRAIK","BROS","BROTHERHOOD","BRUNSWICK","BUCKSBURN","BUDGET","BUILDING","BULAWEYO","BUREAU","BURNTHILLS","BUS","BUSINESS","CABER","CABINS","CABLE","CABS","CAFE","CAIRD","CAIRNCRY","CAIRNGORM","CAKE","CALEDON","CALEDONIA","CALEDONIAN","CALLANDERS","CALOR","CALTEC","CALVARYTABERNACLEUNITED","CAMARGUE","CAMEO","CAMPAIGN","CAMPBELLS","CAMPHILL","CAMWATER","CAN","CANALE","CANCER","CANDO","CANSCO","CAPE","CARADON","CARD","CAREERS","CARGO","CARPET","CARPETS"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"CARRINGTON","CARS","CASTLEHILL","CASTLEPARK","CASTROL","CASUAL","CATC","CATEREASE","CATERING","CATHEAD","CATHERWOOD","CATHOLIC","CATS","CAUSEWAYEND","CAVALIER","CAVANAGH","CEBO","CELEBRATION","CELICA","CELLHIRE","CELTIC","CENTER","CENTRAL","CENTRE","CENTRILIFT","CENTURY","CERAMICS","CH","CHALCO","CHAMBER","CHAMPERS","CHAP","CHARTERED","CHAUFFEUR","CHECKPOINT","CHELL","CHELTENHAM","CHEM","CHEMDRY","CHEMICAL","CHEMICALS","CHEMISTS","CHESS","CHEST","CHILDREN","CHIME","CHINESE","CHIP","CHIROPODY","CHRISTADELPHIAN","CHURCHES","CICT","CIMAGE","CINDERELLA","CINDERS","CINQUE","CIRCLE","CIRCUIT","CIRRUS","CISCO","CITY","CL","CLAN","CLEANALL","CLEANCARE","CLEANING","CLEARWAY","CLEENEZE","CLINIC","CLINICARE","CLIPSO","CLOCKWORK","CLOTHING","CLUB","COALFORD","CODNCIL","COFFEE","COLLEGE","COLLEGES","COLTD","COMMERCE","COMMUNICATIONS","COMMUNITY","COMPANIES","COMPONENT","COMPONENTS","COMPUTER","CONCERN","CONDITIONING","CONFERENCE","CONNECTIONS","CONSERVATORY","CONSTRUCTIN","CONSTRUCTION","CONSULTANTS","CONTINENTAL","CONTRACTS","CONTROL","COOKERS","COT","COUNCELNNG","COUNCIL","COUNCTL","COURIERS","CREMATORIUM","CRICKET","CRYOGENIC","CUISINE","CYRENIAN","CYRENIANS","DEAF","DECORATORS","DENTAL","DES","DESIGN","DETACHED","DEVELOPMENT","DEVELOPMENTS","DEVICES","DIGITAL","DIRECT","DISCOTHEQUE","DISPOSALS","DISTILLERS","DISTRIBUTORS","DIVING","DMLTD","DOMESTIC","DREDGING","DRILLING","DRIVE","DRUGS","DRUM","DRY","EDUCATION","ELECTRICAL","ELEVEN","EMPLOYMENT","ENCOUNTER","ENCOUNTERS","ENDOWMENTS","ENERGY","ENG","ENGINEERING","ENGLNEERING","ENGRAVING","ENL","ENQUIRIES","ENVIRONMENTAL","EQUIPMENT","ERAL","ESL","EXHAUST","EXHAUSTS","EXHIBITION","EXPLORATION","EXPRESS","FABRICATIONS","FACTORS","FARMHOUSE","FASTENERS","FILLING","FILM","FIRE","FISH","FISHERIES","FLORISTS","FLOWERS","FOOD","FOODS","FOODSERVICE","FOOTBALL","FOR","FOUNDRIES","FREE","FUND","FURNISHINGS","FURTHER","GALLERIES","GARAGE","GARMENTS","GAS","GEN","GIRI","GLOUCESTER","GOD","GOLF","GROUP","GUESTHOUSE","HAIR","HANDLING","HEALTH","HEALTHCENTRE","HEATING","HIRE","HO","HOLDINGS","HOMEI","HOTEL","HOUR","HOUSE","HOUSING","HRS","HTG","IA","IECENTRE","IMAGE","IMAGING","IMPELL","IMPERIAL","IN","INC","INCOME","INCORPORATED","INDUATRIAL","INDUSTRIAL","INFANT","INJECTION","INJURY","INN","INORTH","INSPECTION","INSURANCE","INTERNATIONA","INTERNATIONAL","INVESTMENT","INVESTMENTS","IPRIVATE","ISCOUNT","IU","IUKI","JEWELLERS","JEWELLERY","JH","JOINERY","JOURNALS","KITCHEN","KL","KURTUBA","LA","LADLES","LADS","LAUNDERETTE","LAVAL","LEAGUE","LEICESTER","LEISURE","LINK","LIVAL","LOCAL","LOUNGE","LOVELY","LTD","LTDL","MACHINE","MANAGEMENT","MANAGERS","MANIVOFIELD","MARINE","MARKETING","MAZDA","MECHANICAL","MEDICAL","MEDICINE","MENTS","METAL","METERING","MORTGAGE","MOTOR","MOTORING","MOTORS","MOUNTAIN","NDLERS","NEWS","NEWSPAPERS","NTERNATIONAL","NURSERY","NURSING","OF","OFFICE","OFFSHORE","OIL","OILS","OLD","OPTICAL","OPTICIANS","OVE","P0","PAD","PARCELS","PARTNERS","PARTY","PEOPLES","PETROLEUM","PFNTECOSTAL","PHARMACY","PHONE","PHOTOGRAPHIC","PL","PLC","PLMBG","PLUS","PODIATRY","POLLUTION","POWER","PRACTICE","PRE","PRESBYTERIAN","PRIMARY","PRINT","PRINTING","PRODUCTS","PROJECT","PROMOTIONS","PROP","PROPERTY","PROTECTION","PROVINCIAL","PUB","PUBLICATIONS","PUBLISHING","QUALITY","QUALTTY","RADIOGRAPHY","RD","RE","RECREATION","REFRIGERATION","RELOCATIONS","REMOVALS","RENDEZVOUS","RENT","RENTALS","REPAIRS","RESEARCH","RESOURCES","RESTAURANT","RESTAURANTS","RESTNT","RESTORATIONS","RUGBY","SAFETY","SALES","SALON","SAS","SATELLITE","SCHOOL","SCIENCE","SCOTLAND","SEA","SEAL","SEAS","SECURITIES","SERVICE","SERVICES","SHOP","SKIP","SLATING","SMOKED","SOCIAL","SOCIETY","SOFTWARE","SOLICITORS","SONY","SPBRT","SPEC","SPORTS","SRD","STABILISERS","STAMP","START","STATION","STATIONERS","STATIONERY","STAYTAN","STONEYWOOD","STUDIO","SUNBEDS","SUPERBOWL","SUPPLIES","SUPPLTES","SUPPORT","SURVEYS","SYSTEMS","SYSTENIS","TAKE","TAKEAWAY","TAKEAWAYS","TAXIS","TEC","TECHNICAL","TECHNOLOGY","TELEVIDEO","TELEVISION","THE","THISTLE","TO","TOOLS","TOURIST","TRACKING","TRAILER","TRAINING","TRANSFUSION","TRANSMISSION","TRANSPORT","TRAVEL","TROPHIES","TRUST","TUBULARS","TYRE","TYRES","UK","UNION","UNIT","UP","VERITAS","VETCO","VICTIM","VIDEO","VILLAGE","WAYS","WELFARE","WERKE","WHOLESALE","WINDOW","WINDOWS","WINDSCREENS","WITH","WOMEN","WORK","WORLD","YOGA","YOUTH"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$2651").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"Coffee","health","club","distribution","design","solution","taxis","sport","Porsche","garage","shelving","plastics","ndscreens","body","repairs","process","automation","cleaners","signs","management","services","co","school","spares","cooling","domestics","gardening","raceshop","racing","LTD","Taxi","cabs","automotive","electrical","wholesale","enguineering","fabrications","structures","recruitment","construction","contracts","above","products","insurance","brokers","machine","tool","transport","services","trucks","aviation","homestores","associates","joinery","heating","safety","autos","security","supplies","spares","electronics","news","alternative","metal","fabrications","book","keeping","partnership","services","alfa","romeo","fertilizers","reusable","marketing","interiors","pneumatics","quarries","butchers","fresh","frozen","meats","travel","agent","agts","sstem","team","automotive","rubbish","machinery","communication","analytical","café","consumer","products","hotel","rest","precision","grinding","estates","assurance","roofing","office","blinds","business","cettre","caravan","décor","laboratory","dental","hire","national","pharmacy","photo","photographic","picture","framing","press","shop","sports","truck","preservation","preservations","video","society","print","services","pictures","studio","sub","tropical","swannery","packaging","capt","Europe","building","booking","visa","operative","balloon","demolition","driving","fibreglass","employment","flats","properly","waste","welding","window","dirtbuster","gift","inn","hire","mower","flooring","air","the","garage","marketing","residential","dry","pharmacy","glazing","uk","training","british","conference","centre","age","concern","hire","retirement","inspectorate","training","board","financial","age","sales","sale","store","personalized","trust","insurance","squadron","aviation","ventilation","news","international","supplies","rent","est","fabrics","gents","hair","carriage","investments","chips","plc","speciality","gifts","models","anonymous","commercial","surplus","stores","plastia","engineering","collection","chemical","amusement","care","service","antiques","inn","alloys","extrusions","recruitment","maze","sandwich","all","seasons","jazz","weather","carriage","dealers","stores","fairs","fruiterers","butchers","solrs","solicitors","acoustics","accountants","haulage","survy","surveyors","lighting","gospel","disease","insulation","patisserie","mining","amateur","amazon","security","ambassador","snooker","pool","adhesive","Wessex","clinic","wheels","garage","fabrics","express","autos","housing","inn","beautiful","maintenance","opticians","diesel","hire","co","gallery","photography","print","tea","coffee","tackle","store","American","world","continental","educational","roofing","group","sanctuary","beauty","minimarket"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$10000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.Delete Shift : = xlUp
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.ClearContents
Range("A1:A100000").Select ;I just made up the range further notes at the end
Selection.FormulaR1C1 = "=IF(OR(ISNUMBER(search(" "&{"market","pence","bazaar","shave","anorak","bakery","alarm","alarms","bros","applications","partnership","of","restaurant","courtyard","licenced","landscapes","travel","specialist","testing","relay","imaging","environmental","associates","nursing","technology","software","florist","limited","consultant","interiors","exteriors","plasterer","builder","carpenter","mouldings","holdings","vending","logistic","logistics","roofing","fitness","vetinaty","distributors","catalogue","showroom","service","enterprises","cadet","navy","information","centre","clinic","chiropractic","reserve","discount","jewellery","farm","farmers","rafters","pipeline","concrete","fabrications","marketing","performance","engines","bookmaker","peking","exhaust","disposal","guest","recruitment","agencies","estate","golf","panel","healthcare","NHS","estates","driving","clothing","sstems","emergency","gas","handling","hire","nursing","perforators","radio","television","computer","cabs","insulation","insulations","contacts","trust","aerials","satellite","tv","advertising","aquarius","partnership","partners","dental","laboratory","leisure","sound","vision","ear","nose","throat","electrical","advertising","needlecraft","licence","roadhouse","components","repair","products","bod","lynx","spares","mobile","trader","technician","bodywork","autoglass","windshield","unit","flat","units","fl","flats","apartmant","ap","Furnitures","Nursery","Music","Joiners","Fire","Protection","Fabracates","Properties","Out","Filling","Station","Fine","Cafe","ices","tourist","nurseries","lottery","guide","assoc","bus","cafeteria","infirmary","harbour","tarmacadam","fireworks","drafting","deslgn","design","axiom","pyrotenax","furnishing","before","and","after","Bengal","Tandoori","Takeaway","Motors","Drawer","TWAT","Property","Brigade","Advertiser","Quarry","Salon","Bridal","Dreams","Brighton","Selfdrive","Llmousines","Limousines","Distinctive","Furnishers","Motors","Catering","Railway","Produce","Bar","Upholstery","Cleaning","Tyres","Developments","Craft","Citizens","Advice","Bureaux","City","Royal","Royai","Bakeries","Bakery","Filling","Manufacturing","Autobuild","Remarks","Dalhousie","Nite","Chinese","Out","Diamond","Dimensions","Unisex","Dolphin","Fish","Chicken","Catering","Plumbing","Drain","Drainer","Partners","Partnrs","Superbikes","Parrish","CAMERAS","Foods","Food","Epicurus","Productions","Eyeline","Messrs","Home","Toyota","Pet","Bureau","Maternity","Kitchens","Bathrooms","Developments","Seed","Analysis","Guild","Cats","Cradle","Harlequin","Fancy","Dress","Patio","Slabs","Chauffeur","Charter","Highland","Scene","Chrtdarcht","Seafoods","Leader","Jehovah","Jehovahs","Witnesses","Fast","Kashmir","Kebab","Prop","Karriers","Keltic","Self","Drive","Miniature","Fruit","Bazzar","Kleenomat","Laundrette","Uphdlstery","Ser","Llces","Lounge","Brassiere","Lifeboat","Removals","Exchange","Overseas","Link","Kennels","Kennel","Grocer","Family","Fashion","Waves","Making","Boutique","Institute","Mechanics","Medical","Prescriptions","Only","Fellowship","Museum","Port","Authority","Infirmary","Supersavers","Joiners","Pumps","Animal","Feeds","Kong","Hong","New","To","you","suit","midwife","midwives","midwnes","nutters","pottery","and","about","crematorium","potatoes","paws","thought","chip","pets","pantry","handicrafts","dectr","pleasureland","motorcycles","pyramid"}&" "," "&b1&" "))),"YES","NO")"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1, Criteria1 : = "YES"
Rows("x:y").Select ;all row which is YES
Selection.ClearContents
ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field : = 1
Columns("A:A").Select
Selection.Delete Shift : = xlToLeft ;Delete the Whole column. The job is done, dont need more.

ActiveWorkbook.SaveAs Filename : = _
        "C:\Users\user\Documents\THEDOCUMENTNAME_cleaning in progress.xlsx" _ ;just a save as with adding to the name "_cleaning in progress"
        ;I used for Range A1:A100000 however every excel file contains different number of rows. If there is anyway just select where actually any data than I prefer to that otherwise if i select the whole column the program want to fil in more than a million cell. That is just killin' my i5 proc+8gb ram.

So yeah this is it... Any suggestion?

[water]

Recording a macro is very static. Means: The recorded script does exactly what you have done when recording. To make it more dynamic you need to understand what was recorded and how Excel works.

Another problem is that the recorded script works with the GUI (selection etc.) which makes it rather slow.

If you want to use my Excfel UDF I suggest the following:

1. Insert a New Column (should be B but doesnt really matter): _Excel_RangeInsert
2. add a formula to the whole Column (Ctrl + Enter): _Excel_RangeWrite
3. If the result is Yes DELETE the whole row which are yes (I using Kutools for Excel when i doing manually by hand): _Excel_RangeFilter and some manual processing with .EntireRow.Delete
4. after clear the coumn: Nothing needed if the next step writes to the same column
5. add a second formula: _Excel_RangeWrite
6. if yes delete the whole rows again: Same as above
7. after delete the whole column: _Excel_RangeDelete

Start with 1. and 2. If successfull I will have a look at 3.

[water]

Example:

#include <Excel Rewrite.au3>
Global $oExcel = _Excel_Open() ; Connects to Excel or starts a new instance
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") ; Opens _Excel1 workbook in the directory where the script resides
_Excel_RangeInsert($oWorkbook.Activesheet, "B:B", $xlShiftToRight) ; Inserts a new column B and shifts the other columns to the right
_Excel_RangeWrite($oWorkbook, Default, "=MOD(A1;2)", "B:B", False) ; Inserts the formula to mark even row numbers with 0 
_Excel_FilterSet($oWorkbook, Default, "B:B", 1, "0") ; Only displays even row numbers

Edited October 14, 2013 by water

[water]

Another example. In addition all visible rows are deleted.

#include <Excel Rewrite.au3>
Global $aData[][] = [[" ", " "], [1, "1-2"], [2, "2-2"], [3, "3-2"], [4, "4-2"]]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
_Excel_RangeInsert($oWorkbook.Activesheet, "B:B", $xlShiftToRight) ; Insert column B and shift existing data to the right
_Excel_RangeWrite($oWorkbook, Default, "=Rest(A2;2)", "$B2:B" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 2, "0") ; Only display even numbers
$oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
$oRange.EntireRow.Delete ; delete visible rows

[zsoltm]

#include <Excel Rewrite.au3>
#include <Array.au3>

Dim $selector = """quality"",""st"",""rainbow"",""carryout"",""auto"",""surgery"",""public"",""fertiliser"",""Fertilisers"",""ifertilisersl"",""rendezvous"",""remember"",""that"",""precast"",""barefoot"",""mail"",""scaffolding"",""safe"",""access"",""salvation"",""army"",""funeral"",""directors"",""director"",""seahorse"",""simply"",""affordable"",""stich"",""time"",""dressmaking"",""alterations"",""hospital"",""by"",""jewellers"",""post"",""sweet"",""sensation"",""trophies"",""engraving"",""protection"",""produce"",""public"",""relations"",""made"",""fun"",""auctions"",""auction"",""kiltmakers"",""kiltmaker"",""linen"",""chippy"",""timber"",""tidy"",""tangles"",""past"",""dealer"",""trading"",""crafts"",""craft"",""naval"",""activities"",""US"",""Uncle"",""Diner"",""Removals"",""Volunteer"",""Zoo"",""Wavelength"",""Wavelengths"",""Friends"",""hairdressing"",""theatreclub"",""daynursery"",""MUSLC"",""superstore"",""colleges"",""AALCO"",""AARDVARK"",""ABACUS"",""ABB"",""ABBOTSWELL"",""ABBOTTPUBLIC"",""ABC"",""ABCON"",""ABDN"",""ABERCARE"",""ABERDEE"",""ABERDEEN"",""ABERDEENSHIRE"",""ABERFLORA"",""ABERGELDIE"",""ABERGLEN"",""ABERLOUR"",""ABERMED"",""ABERNE"",""ABERPEST"",""ABERSOL"",""ABERT"",""ABSCOT"",""ABSOFT"",""ABSPEK"",""ABSURE"",""ABTEX"",""ABTRUST"",""ACADEMY"",""ACCESS"",""ACCESSORIES"",""ACCOMMODATION"",""ACCORD"",""ACCOUNTANCY"",""ACCURAY"",""ACE"",""ACTION"",""ADAPT"",""ADEPT"",""ADMIN"",""ADMIRAL"",""ADS"",""ADULT"",""ADVANCE"",""ADVANCED"",""ADVANT"",""ADVANTAGE"",""ADVICE"",""AEA"",""AFFAIR"",""AFOS"",""AGA"",""AGE"",""AGENCY"",""AGIP"",""AGRI"",""AGRICULTURAL"",""AGRICULTURE"",""AHT"",""AI"",""AIDS"",""AIR"",""AIRBORNE"",""AIRFAIR"",""AIRLINES"",""AIRNAUTIC"",""AIRPAC"",""AIRPORT"",""AIRWAYS"",""AIRYHALL"",""AIRYLEA"",""AKRON"",""ALAD"",""ALASDAIR"",""ALB"",""ALBATECH"",""ALFA"",""ALL"",""ALLIANCE"",""ALLIED"",""ALLOMAX"",""ALPINE"",""ALSTEV"",""ALTERNATIVES"",""ALTRA"",""ALWAYS"",""ALZHEIMER"",""AM"",""AMALGAMATED"",""AMAT"",""AMATOLA"",""AMBER"",""AMEC"",""AMERICAN"",""AMETEK"",""AMIN"",""AMITEC"",""AMOCO"",""AMTECH"",""AMTRAK"",""AMUSEMENT"",""AND"",""ANDERGAUGE"",""ANIXTER"",""ANKA"",""ANTIQUES"",""ANTRIM"",""AOC"",""AP"",""APARDION"",""APEX"",""APOLLO"",""APOSTOLIC"",""APPEALS"",""APPLIED"",""APPOLLO"",""AQUA"",""AQUARISTS"",""AQUATEC"",""AQUATIC"",""AQUIDATA"",""ARCHITECTURAL"",""ARCO"",""ARGONAUT"",""ARGOSY"",""ARISTACUT"",""ARJO"",""ARJON"",""ARKAIG"",""ARTISTES"",""ARTISTS"",""ARTWORKS"",""ARUP"",""AS"",""ASA"",""ASCOT"",""ASDASUPERSTORES"",""ASHVALE"",""ASPECT"",""ASSEMBLIES"",""ASSESSORS"",""ASSOCIATED"",""ASSOCIATES"",""ASSOCIATION"",""ASSURANCE"",""ASTER"",""ASTRA"",""ATEL"",""ATHENA"",""ATHENAEUM"",""ATHOLL"",""ATLANTIC"",""AUCHMILL"",""AUCHTERLESS"",""AUGHTON"",""AUGUST"",""AUP"",""AUQUHARNEY"",""AURORA"",""AUTO"",""AUTOFIX"",""AUTOGLASS"",""AUTOMAGIC"",""AUTOQUICK"",""AUTOSAVE"",""AUTOSPRAY"",""AUTOTRUCK"",""AVCO"",""AVENUE"",""AWAKENING"",""AWAY"",""AXIOM"",""AZTEC"",""BAADER"",""BALNAGASK"",""BANKHEAD"",""BAR"",""BARICO"",""BARRIER"",""BARTERING"",""BASELINE"",""BATCHBRIGHT"",""BATH"",""BATHROOM"",""BAVARIA"",""BAYLISS"",""BAYLOR"",""BEARING"",""BEATTIES"",""BEAUTY"",""BEAVERBROOKS"",""BEECHWOOD"",""BEEFEATER"",""BEELINE"",""BEFRIEND"",""BENBOW"",""BESTWAY"",""BEWS"",""BIKES"",""BLIND"",""BLINDCRAFT"",""BLINDS"",""BLOOD"",""BLOOMING"",""BLOSSOMS"",""BLOW"",""BLUEBELLS"",""BMT"",""BNA"",""BOARD"",""BOARS"",""BOAT"",""BODY"",""BOHEMIA"",""BOILERMAKERS"",""BON"",""BONADDIO"",""BONAVENTURE"",""BONIVIEW"",""BONNYMUIR"",""BOOKBINDING"",""BOOTS"",""BOROWSKI"",""BORSALINO"",""BOSANQUET"",""BOSCH"",""BOTTLED"",""BOULEVARD"",""BOURTREE"",""BOWEN"",""BOWLING"",""BOWTECH"",""BOX"",""BP"",""BPCC"",""BRA"",""BRACKENRIDGE"",""BRAEHEAD"",""BRAESIDE"",""BRAKE"",""BRIEF"",""BRITANNIA"",""BRITANNIC"",""BROAD"",""BROADCAST"",""BROADSTRAIK"",""BROS"",""BROTHERHOOD"",""BRUNSWICK"",""BUCKSBURN"",""BUDGET"",""BUILDING"",""BULAWEYO"",""BUREAU"",""BURNTHILLS"",""BUS"",""BUSINESS"",""CABER"",""CABINS"",""CABLE"",""CABS"",""CAFE"",""CAIRD"",""CAIRNCRY"",""CAIRNGORM"",""CAKE"",""CALEDON"",""CALEDONIA"",""CALEDONIAN"",""CALLANDERS"",""CALOR"",""CALTEC"",""CALVARYTABERNACLEUNITED"",""CAMARGUE"",""CAMEO"",""CAMPAIGN"",""CAMPBELLS"",""CAMPHILL"",""CAMWATER"",""CAN"",""CANALE"",""CANCER"",""CANDO"",""CANSCO"",""CAPE"",""CARADON"",""CARD"",""CAREERS"",""CARGO"",""CARPET"",""CARPETS"""
Dim $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b1&"" ""))),""YES"",""NO"")"
Global $aData[][] = [[$brformula], [2], [3], [4],[1], [2], [3], [4],[1], [2], [3], [4],[1] , [2], [3], [4]]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeInsert($oWorkbook.Activesheet, "A:A", $xlShiftToRight) ; Insert column B and shift existing data to the right
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
_Excel_RangeWrite($oWorkbook, Default, "=Rest(A2;2)", "$A2:A" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 1, "1") ; Only display even numbers
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
$oRange.EntireRow.Delete ; delete visible rows

whats wring with this? its executing but not doing anything only opens the excel... but if not put the [$brformula] to the $aData at least writeing the other numbers to the columns.

[water]

My fault. Posted a wrong formula (german).

"=Rest(A2;2)"

needs to be the name of the modulo function

"=MOD(A2;2)"

[zsoltm]

its giving me error for this as well. ==> Variable used without being declared.:

Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible)

Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells(^ ERROR


and still with the MOD its not writing nothing to excel. does my variables are correct?

 

edit: if just using simple texts its working but when i want to put the variable than nothing.

Edited October 16, 2013 by zsoltm

[water]

I modified the Constants include file too. Add this line to your script:

Global Const $xlCellTypeVisible = 12 ; All visible cells

[water]

Can you post a screenshot of the workbook after you manually entered the data?

[zsoltm]

 

this is what I mean. of course when it will work correctly i want to change the settings to open already existing xls files. make an automatation when one is ready open the next one and start again ..if its possible

[water]

The following script works fine here.

#include <Excel Rewrite.au3>

;Global $selector = """quality"",""st"",""rainbow"",""carryout"",""auto"",""surgery"",""public"",""fertiliser"",""Fertilisers"",""ifertilisersl"",""rendezvous"",""remember"",""that"",""precast"",""barefoot"",""mail"",""scaffolding"",""safe"",""access"",""salvation"",""army"",""funeral"",""directors"",""director"",""seahorse"",""simply"",""affordable"",""stich"",""time"",""dressmaking"",""alterations"",""hospital"",""by"",""jewellers"",""post"",""sweet"",""sensation"",""trophies"",""engraving"",""protection"",""produce"",""public"",""relations"",""made"",""fun"",""auctions"",""auction"",""kiltmakers"",""kiltmaker"",""linen"",""chippy"",""timber"",""tidy"",""tangles"",""past"",""dealer"",""trading"",""crafts"",""craft"",""naval"",""activities"",""US"",""Uncle"",""Diner"",""Removals"",""Volunteer"",""Zoo"",""Wavelength"",""Wavelengths"",""Friends"",""hairdressing"",""theatreclub"",""daynursery"",""MUSLC"",""superstore"",""colleges"",""AALCO"",""AARDVARK"",""ABACUS"",""ABB"",""ABBOTSWELL"",""ABBOTTPUBLIC"",""ABC"",""ABCON"",""ABDN"",""ABERCARE"",""ABERDEE"",""ABERDEEN"",""ABERDEENSHIRE"",""ABERFLORA"",""ABERGELDIE"",""ABERGLEN"",""ABERLOUR"",""ABERMED"",""ABERNE"",""ABERPEST"",""ABERSOL"",""ABERT"",""ABSCOT"",""ABSOFT"",""ABSPEK"",""ABSURE"",""ABTEX"",""ABTRUST"",""ACADEMY"",""ACCESS"",""ACCESSORIES"",""ACCOMMODATION"",""ACCORD"",""ACCOUNTANCY"",""ACCURAY"",""ACE"",""ACTION"",""ADAPT"",""ADEPT"",""ADMIN"",""ADMIRAL"",""ADS"",""ADULT"",""ADVANCE"",""ADVANCED"",""ADVANT"",""ADVANTAGE"",""ADVICE"",""AEA"",""AFFAIR"",""AFOS"",""AGA"",""AGE"",""AGENCY"",""AGIP"",""AGRI"",""AGRICULTURAL"",""AGRICULTURE"",""AHT"",""AI"",""AIDS"",""AIR"",""AIRBORNE"",""AIRFAIR"",""AIRLINES"",""AIRNAUTIC"",""AIRPAC"",""AIRPORT"",""AIRWAYS"",""AIRYHALL"",""AIRYLEA"",""AKRON"",""ALAD"",""ALASDAIR"",""ALB"",""ALBATECH"",""ALFA"",""ALL"",""ALLIANCE"",""ALLIED"",""ALLOMAX"",""ALPINE"",""ALSTEV"",""ALTERNATIVES"",""ALTRA"",""ALWAYS"",""ALZHEIMER"",""AM"",""AMALGAMATED"",""AMAT"",""AMATOLA"",""AMBER"",""AMEC"",""AMERICAN"",""AMETEK"",""AMIN"",""AMITEC"",""AMOCO"",""AMTECH"",""AMTRAK"",""AMUSEMENT"",""AND"",""ANDERGAUGE"",""ANIXTER"",""ANKA"",""ANTIQUES"",""ANTRIM"",""AOC"",""AP"",""APARDION"",""APEX"",""APOLLO"",""APOSTOLIC"",""APPEALS"",""APPLIED"",""APPOLLO"",""AQUA"",""AQUARISTS"",""AQUATEC"",""AQUATIC"",""AQUIDATA"",""ARCHITECTURAL"",""ARCO"",""ARGONAUT"",""ARGOSY"",""ARISTACUT"",""ARJO"",""ARJON"",""ARKAIG"",""ARTISTES"",""ARTISTS"",""ARTWORKS"",""ARUP"",""AS"",""ASA"",""ASCOT"",""ASDASUPERSTORES"",""ASHVALE"",""ASPECT"",""ASSEMBLIES"",""ASSESSORS"",""ASSOCIATED"",""ASSOCIATES"",""ASSOCIATION"",""ASSURANCE"",""ASTER"",""ASTRA"",""ATEL"",""ATHENA"",""ATHENAEUM"",""ATHOLL"",""ATLANTIC"",""AUCHMILL"",""AUCHTERLESS"",""AUGHTON"",""AUGUST"",""AUP"",""AUQUHARNEY"",""AURORA"",""AUTO"",""AUTOFIX"",""AUTOGLASS"",""AUTOMAGIC"",""AUTOQUICK"",""AUTOSAVE"",""AUTOSPRAY"",""AUTOTRUCK"",""AVCO"",""AVENUE"",""AWAKENING"",""AWAY"",""AXIOM"",""AZTEC"",""BAADER"",""BALNAGASK"",""BANKHEAD"",""BAR"",""BARICO"",""BARRIER"",""BARTERING"",""BASELINE"",""BATCHBRIGHT"",""BATH"",""BATHROOM"",""BAVARIA"",""BAYLISS"",""BAYLOR"",""BEARING"",""BEATTIES"",""BEAUTY"",""BEAVERBROOKS"",""BEECHWOOD"",""BEEFEATER"",""BEELINE"",""BEFRIEND"",""BENBOW"",""BESTWAY"",""BEWS"",""BIKES"",""BLIND"",""BLINDCRAFT"",""BLINDS"",""BLOOD"",""BLOOMING"",""BLOSSOMS"",""BLOW"",""BLUEBELLS"",""BMT"",""BNA"",""BOARD"",""BOARS"",""BOAT"",""BODY"",""BOHEMIA"",""BOILERMAKERS"",""BON"",""BONADDIO"",""BONAVENTURE"",""BONIVIEW"",""BONNYMUIR"",""BOOKBINDING"",""BOOTS"",""BOROWSKI"",""BORSALINO"",""BOSANQUET"",""BOSCH"",""BOTTLED"",""BOULEVARD"",""BOURTREE"",""BOWEN"",""BOWLING"",""BOWTECH"",""BOX"",""BP"",""BPCC"",""BRA"",""BRACKENRIDGE"",""BRAEHEAD"",""BRAESIDE"",""BRAKE"",""BRIEF"",""BRITANNIA"",""BRITANNIC"",""BROAD"",""BROADCAST"",""BROADSTRAIK"",""BROS"",""BROTHERHOOD"",""BRUNSWICK"",""BUCKSBURN"",""BUDGET"",""BUILDING"",""BULAWEYO"",""BUREAU"",""BURNTHILLS"",""BUS"",""BUSINESS"",""CABER"",""CABINS"",""CABLE"",""CABS"",""CAFE"",""CAIRD"",""CAIRNCRY"",""CAIRNGORM"",""CAKE"",""CALEDON"",""CALEDONIA"",""CALEDONIAN"",""CALLANDERS"",""CALOR"",""CALTEC"",""CALVARYTABERNACLEUNITED"",""CAMARGUE"",""CAMEO"",""CAMPAIGN"",""CAMPBELLS"",""CAMPHILL"",""CAMWATER"",""CAN"",""CANALE"",""CANCER"",""CANDO"",""CANSCO"",""CAPE"",""CARADON"",""CARD"",""CAREERS"",""CARGO"",""CARPET"",""CARPETS"""
;Global $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b1&"" ""))),""YES"",""NO"")"
Global Const $xlCellTypeVisible = 12 ; All visible cells
Global $aData[] = [" ", 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4]
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook
_Excel_RangeWrite($oWorkbook, Default, $aData, "A1") ; Write the array to the workbook
MsgBox(0, "RangeWrite Data", @error)
_Excel_RangeWrite($oWorkbook, Default, "=Mod(A2;2)", "$B2:B" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B
MsgBox(0, "RangeWrite Formula", @error)
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 2, "1") ; Only display even numbers
MsgBox(0, "FilterSet", @error)
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
MsgBox(0, "Visible cells", @error)
$oRange.EntireRow.Delete ; delete visible rows

Each MsgBox shows the result. Click OK to proceed to the next step.

[water]

BTW: The definition of $selector doesn't work because the line is too long (>4096 characters).

[zsoltm]

FYI not even 4k characters just around 240~   :S i have 17k~ what a wanna do... arrays.
but with that works ...
next step is in the A column i just want to use my formula what I mean 
A1 checks B1 
A2 checks B2

A3 checks B3
... every row while we had filled every row  which are not empty.The other documents can be 5000 rows can be 800.000 rows

Global $brformula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"

For $i = 1 to (maximum row) Step 1

Edited October 16, 2013 by zsoltm

[zsoltm]

In the Scite help i found the following code:
 

do
         local var, _limit, _step = tonumber(e1), tonumber(e2), tonumber(e3)
         if not (var and _limit and _step) then error() end
         while (_step>0 and var<=_limit) or (_step<=0 and var>=_limit) do
           block
           var = var + _step
         end
       end

but in the scite is looks like not recognize the end word .. 
I tried to do the following:

Do
    Local $i, 100, step = 1
    if not ( $i and 100 and 1) then error() EndFunc
    while (100>0 and $i<=100) or (100<=0 and $i>=100 do
_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = $i)
 $i = $i + 1
 WEnd
 End

[13lack13lade]

While you can definitely do this in AutoIt i would say, it would be alot easier to accomplish this using VBA in excel.

Not trying to diss autoit in anyway    but for what you are wanting to accomplish, so much easier to do it with VBA especially with the macro record tool in Excel.

Then if you want it in a GUI in Autoit you can simply have an autoit import/call the macro within the excel spreadsheet.

Edited October 17, 2013 by 13lack13lade