stick3r Posted September 30, 2018 Share Posted September 30, 2018 Hi, I have excel with 15k rows and I need to filter it. The problem is, that I have 5 digits number and I need to find all values starting with that 5 digits. E.g. I have 33333, and I need to find all values starting with 33333. It could be 3333311, 3333322, 3333369, 3333399....... $oExcel.ActiveSheet.Columns("A").AutoFilter(1, "33333", $xlFilterValues) This line does not work, as it looks exactly for "33333". What is the best way to get all values starting with 33333 from Column A ? Link to comment Share on other sites More sharing options...
FrancescoDiMuro Posted September 30, 2018 Share Posted September 30, 2018 @stick3r This guide seems to be very helpful Try to change your code with $oExcel.ActiveSheet.Columns("A").AutoFilter(1, "33333*", $xlFilterValues) The asterisk is used as wildcard ( as it is in many other languages ), to describe the operation as "Match everything that starts with 33333". In your case, you were searching exactly for the number 33333. You can use wildcards wherever you want... At the start of your string, in the middle, at the end, both... Try it and let us know Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette Link to comment Share on other sites More sharing options...
stick3r Posted September 30, 2018 Author Share Posted September 30, 2018 (edited) I does not work for me Although when I have a mouse on filtered A1, it says Begin with 33333, but all rows in excel are empty like there would be nothing to show with this 33333* criteria Edited September 30, 2018 by stick3r Link to comment Share on other sites More sharing options...
water Posted September 30, 2018 Share Posted September 30, 2018 Are the values stored as text or numbers in Excel? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
stick3r Posted September 30, 2018 Author Share Posted September 30, 2018 At the beginning it was General, but I have tried Text and Numbers now, still the same empty filtered excel. Link to comment Share on other sites More sharing options...
FrancescoDiMuro Posted September 30, 2018 Share Posted September 30, 2018 (edited) @stick3rA possible solution: expandcollapse popup#include <Array.au3> #include <Excel.au3> #include <ExcelConstants.au3> Global $objExcel, _ $objWorkbook, _ $objWorkbook, _ $strFilePath = @ScriptDir & "\ExcelSample.xlsx", _ $arrExcelRangeRead, _ $arrExcelRangeReadFiltered[1] $objExcel = _Excel_Open(False) If @error Then ConsoleWrite("Error while creating Excel object. Error: " & @error & @CRLF) Exit Else $objWorkbook = _Excel_BookOpen($objExcel, $strFilePath) If @error Then ConsoleWrite("Error while opening the file '" & $strFilePath & "'. Error: " & @error & @CRLF) Else $arrExcelRangeRead = _Excel_RangeRead($objWorkbook, $objWorkbook.ActiveSheet, $objWorkbook.ActiveSheet.Usedrange.Columns("A:A")) If @error Then ConsoleWrite("Error while reading the specified range. Error: " & @error & @CRLF) Else _ArrayDisplay($arrExcelRangeRead, "Rows:") For $i = 0 To UBound($arrExcelRangeRead) - 1 If StringLeft($arrExcelRangeRead[$i], 5) = "33333" Then _ArrayAdd($arrExcelRangeReadFiltered, $arrExcelRangeRead[$i]) EndIf Next $arrExcelRangeReadFiltered[0] = UBound($arrExcelRangeReadFiltered) _ArrayDisplay($arrExcelRangeReadFiltered, "Rows filtered:") EndIf _Excel_BookClose($objWorkbook) _Excel_Close($objExcel) EndIf EndIf Edited September 30, 2018 by FrancescoDiMuro Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette Link to comment Share on other sites More sharing options...
water Posted September 30, 2018 Share Posted September 30, 2018 Did you have a look at _Excel_FilterSet? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
stick3r Posted September 30, 2018 Author Share Posted September 30, 2018 Thanks, @FrancescoDiMuro This is a possible solution, but it would work better for me to have all columns together with filtered numbers, as there is also important information in other columns I will have to work with. @water, I have found a solution with _Excel_FilterSet, as $mynumber should always be 5 digits and then 3 extra, so this should find everything I need. _Excel_FilterSet($oWorkbook, Default, "A1", 1, ">=" & $mynumber & "000", 1, "<=" & $mynumber & "999") I have found something strange with asterisk code. $oExcel.ActiveSheet.Columns("B").AutoFilter(1, "A*", $xlFilterValues) This actually works on other columns and find everything starting with "A", but not on column A with the numbers. I have tried to copy paste column A to any other, but still it would not filter correctly. I will try to play with it and maybe I will find the issue, and if you will come up with some ideas, please come back. Thanks for your help. Link to comment Share on other sites More sharing options...
water Posted September 30, 2018 Share Posted September 30, 2018 (edited) Usually I define a range as starting:ending column. Could you try „B:B“? Edited September 30, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FrancescoDiMuro Posted September 30, 2018 Share Posted September 30, 2018 @water Shouldn't be $objWorksheet.ActiveSheet.Columns("B:B").AutoFilter(...) And not $objExcel.ActiveSheet.Columns("B:B").AutoFilter(...) ? Cheers Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette Link to comment Share on other sites More sharing options...
water Posted September 30, 2018 Share Posted September 30, 2018 Should be $oWorkbook.ActiveSheet or $oExcel.ActiveSheet FrancescoDiMuro 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
FrancescoDiMuro Posted September 30, 2018 Share Posted September 30, 2018 @water Thanks for the tip! Didn't know that it could be written in both ways Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now