Jfish Posted October 30, 2014 Share Posted October 30, 2014 I searched around for the best way to this and am still a bit lost. I am trying to find the last non-empty cell in a column in Excel. In the past, there was a function _ExcelSheetUsedRangeGet($oExcel, $vSheet) in >this thread. However, much has changed since then and when I downloaded it I could not find that function to see how it worked. Google found this site for me which offered a couple of promising looking approaches: In any case, you should now use [All versions]: Dim LastLine As Long LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row or : Dim LastLine As Long LastLine = Range("A" & Rows.Count).End(xlUp).Row They are in VBA. I usually can convert VBA but I am not 100% on these. The first approach looks a lot like it should convert easily to _Excel_RangeFind but I am not familiar enough with all the parameters. I understand the operators referenced in the help file but I did not see a lot of info on XLFindLookIn and not sure what to do with .Row. I am also not sure if these are the best approaches? Any guidance on the best way to do this would be much appreciated. If this is already part of _Excel_RangeFind I could use an example. Any help would be greatly appreciated. JFish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
Radiance Posted October 30, 2014 Share Posted October 30, 2014 Hi, I'm using _ExcelReadSheetToArray to read the whole file. $return[0][0] is usually the last non-empty row. Link to comment Share on other sites More sharing options...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @radiance - thanks. I know about that way but that is a deprecated function and will take up a lot of memory / time on a large sheet. Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
Solution water Posted October 30, 2014 Solution Share Posted October 30, 2014 Use function _Excel_RangeRead and set parameter $vRange = Default to only read the used cells of a worksheet. hugomito 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @Water - Thanks! I can see the array and the size of the array is what I need. Seems fast too - I only have 567 rows in the test file but I expect a lot more. How does the performance compare to the prior _ExcelReadSheetToArray function? Just curious. Also, here is what I did in case anyone searches this thread: #include <Array.au3> #include<Excel.au3> Local $oAppl = _Excel_Open() Local $sWorkbook = @ScriptDir & "\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) $lastline=_Excel_RangeRead ($oWorkbook,Default,Default,Default) _ArrayDisplay($lastline) JFish hugomito 1 Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 The new function is about 20 to 100 times faster. 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @water - WOW. That prompts another question... there must be a more efficient way to delete all blank rows than what I am doing? for $a=UBound($lastline) to 1 step -1 $range="A"&$a $rowValue=_Excel_RangeRead($oWorkbook,Default,$range) if $rowValue=="" Then $delete=_Excel_RangeDelete($oWorkbook.ActiveSheet,$range,default,1) EndIf Next Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 Why read each cell in the loop again? Check the value in the array $lastline and if blank delete the appropriate cell in the worksheet. 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 Not enough coffee this AM that's why ... if that is removed - is looping the array the best approach? Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 Please define "best" 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 Best: "Fastest" Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 Not that I know of. But maybe Google shows some faster ways? 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...
water Posted October 30, 2014 Share Posted October 30, 2014 Example: Start the Excel macro recorder, do as described here (http://www.theexceladdict.com/_t/t031008.htm) and translate the VBA code to AutoIt. 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @Water - Thanks! I will look at that. JFish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @Water - I think it should be: $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells("xlCellTypeBlanks").EntireRow.Delete but it does not seem to work. Any suggestions? Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 Add #include <Debug.au3> _DebugSetup() _DebugCOMError() at the top of your script to get more defailed error information. 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 (edited) Thx. "SpecialCells method of Range class failed" xlCellTypeBlanks is the right type of special cell. Do I have the syntax for that wrong? EDIT: I refined the range to the last used cell and now have this error: @@ DEBUG COM Error encountered in deleteblankrows.au3 (19) : Number = 0x80020009 (-2147352567) WinDescription = Exception occurred. Description = Source = HelpFile = HelpContext = 0 LastDllError = 0 Retcode = 0x800A03EC >>>>>> Please close the "Report Log Window" to exit <<<<<<< Edited October 30, 2014 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 The cell type needs to be numeric. Please give this code a try: Global Const $xlCellTypeBlanks = 4 $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete 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...
Jfish Posted October 30, 2014 Author Share Posted October 30, 2014 @Water - That did not work because it said "cannot re-declare a constant" for Global Const $xlCellTypeBlanks = 4 but this did work!: $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells(4).EntireRow.Delete That's pretty cool! Thanks very much for your help. I know that was a bit of an investment in time on your side. Also, I respectfully submit that this could be a cool UDF feature. Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted October 30, 2014 Share Posted October 30, 2014 Then the constant is already declared in the UDF. So the following line would be enough. Using the constant makes the code much easier to read and understand when you look into it in a few months $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete 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...
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