cnilsson76

I am trying to use _Excel_FilterSet to "clear" a filter from a column in a spreadsheet. By "clear" I mean that I want to show all values in the column...in other words "turn the filter off". However, I do not want to remove the filter. _Excel_FilterSet($oWorkbook, Default, Default, 0) This option REMOVES all filters from all columns in the spreadsheet. _Excel_FilterSet($oWorkbook, Default, Default, 4) Oddly, this code also removes all column filters (not just column D) - Is this the expected behavior? _Excel_FilterSet($oWorkbook, Default, Default, 4, "<>") This option will keep the filters and select all non-blanks in column D as expected. _Excel_FilterSet($oWorkbook, Default, Default, 4, "") This option will keep the filters and select all blanks in column D as expected . I'm looking for a combination of the last two. I want to keep the filter on the column but show all values including blanks. The equivalent of the "(Select All)" option if you were interacting with the filter directly in Excel. If I were in VBA, I would simply use Worksheet.Range["A1"].AutoFilter(1); to clear all the filters on the sheet (same as clicking Select All) Is this possible? Thanks!

This is perfect! The syntax for how to reference a named table and column was the missing part for me. $oTable = $oWorksheet.ListObjects($sTable) With $oTable.ListColumns($iColumn).Range $iLastRow = .Find("*", .Cells(1), $xlFormulas, $xlPart, $xlByRows, $xlPrevious, False).Row EndWith I had poured over the docs but was struggling to merge Excels dot notation with AutoIT. Thank you for your help!

I have been successfully finding the last used row of Excel sheets with the following line. $oRange = $oWorkbook.Sheets("Raw Data").UsedRange.SpecialCells($xlCellTypeLastCell) This works perfectly if there is no formatting in the sheet. However, if there is an empty cell with any formatting (highlighting, borders, defined number format, etc.) then that is the returned range. This is the correct behavior of the .UsedRange property and is expected. However, I need to find the last row containing data without regard to any formatting. The sheet I am searching has a defined Excel table object covering A:K for the Max length Excel will allow (=$A$2:$K$1048471). This means that all the cells in the table have been "touched" by a user/script and thus the Used.Range function does not return the last row with actual data. I found this post on stackoverflow which includes a great answer for VBA users. In has a section for "Find Last Row in a Sheet" With Sheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lastrow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lastrow = 1 End If End With I have tried to use the _Excel_RangeFind function with the values for LookAt, LookIn, etc. from the VBA above plugged in but every permutation generates an error. Just one failed example is below. Local $oRange = _Excel_RangeFind($oWorkbook.Sheets("Raw Data"), "*", .Range("A1")) The stackoverflow post also has a section called "Find Last Row in a Table (ListObject)" which is exactly what I need to do, however, I have no idea how to translate this into AutoIT. Sub FindLastRowInExcelTableColAandB() Dim lastRow As Long Dim ws As Worksheet, tbl as ListObject Set ws = Sheets("Sheet1") 'Modify as needed 'Assuming the name of the table is "Table1", modify as needed Set tbl = ws.ListObjects("Table1") With tbl.ListColumns(3).Range lastrow = .Find(What:="*", _ After:=.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row End With End Sub I have also seen methods that talk about reading up from the bottom until you reach some data but I'm concerned about performance with the table range being so large. Are there any sure fire ways to find the last used row (used meaning not blank) in an Excel table while ignoring all formatting?

This method worked as well...thank you for the help!

Thank you so much. The addition of the \D \b did the trick! Thank you for your help! Correctly functioning code below for any future users searching for this answer. #include <MsgBoxConstants.au3> Global $aValues[22] = ["B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14", "B15", "B16", "B17", "B18", "B19", "B20", "B21", "B22", "B23"] For $i = 0 To 21 $iMatch = StringRegExp($aValues[$i], "\D(3|6|9|12|15|18|21)\b") $Found = (($iMatch = 1) ? ("Found") : ("Not found") ) MsgBox($MB_SYSTEMMODAL, "Result", $Found & " in:" & @CRLF & $aValues[$i]) Next

Thanks for the reply, however, this pattern fails in the same way. It matches "B13", "B16", "B19", and "B23" because they have a 3, 6, or 9 in the number. 3, 6, and 9 are valid matches but I only want them to match if they are the complete number. I want to match "3" but not match "13" or "23".

I need to match a number in a string to a specific list of numbers via StringRegExp. I have successfully built an expression that matches the numbers to a list of possible matches but the two digit numbers are causing problems. #include <MsgBoxConstants.au3> Global $aValues[22] = ["B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14", "B15", "B16", "B17", "B18", "B19", "B20", "B21", "B22", "B23"] For $i = 0 To 21 $iMatch = StringRegExp($aValues[$i], "(3)|(6)|(9)|(12)|(15)|(18)|(21)") $Found = (($iMatch = 1) ? ("Found") : ("Not found") ) MsgBox($MB_SYSTEMMODAL, "Result", $Found & " in:" & @CRLF & $aValues[$i]) Next As you can see, I want to only match if the string contains the literal whole number 3, 6, 9, 12, 15, 18, or 21. I mean the entire number. So three literally means "3". I don't want the number 13 or 23 to trigger a match. So, "B6" should evaluate as a match but "B16" should not. I have tried many variations of the RegEx but the pattern above is the closest I can get. Thank you all for your help!

Fantastic! Worked perfectly. For anyone stumbling upon this years later...the @CRLF does not go in quotes like a string of text would. The final code inside the loop looks like $description &= @CRLF & $aResult[$r][1]

I'm trying to enter text into a text area (_IEFormElementSetValue) that includes new line entries. I can do this by setting the focus on the text area element and simulating key strokes including the enter key for new lines. The problem is that the data I'm entering is built during a loop with other events happening. I'm quite certain I could still programmatically set the focus at the correct time...right arrow to get to the end of the line and enter the next line by simulating key strokes...but I'm hoping for something more elegant. Here is what I'm trying to do in the loop $description = $aResult[$r][1] For... $description &= "\r\n" & $aResult[$r][1] Next _IEFormElementSetValue ($text-area, $description) Clearly the "rn" is not correct. Does anyone know what the string would be to cause a new line in the text area? THANKS