Forum Discussion
My VBA copies row if cell NOTBLANK but I need target columns in specific order (Code in post)
Kenneth Green The <> operator on its own will only work if the cells in column K are truly blank. Since it's not working for you, I'm guessing the cells contain zero-length strings, most likely the result of a formula returning "". In this case, you can try using a formula as the criteria. There are 3 rules when using formulas in the criteria range:
- No heading (or use a custom heading not found in the data range, ie: "Criteria1").
- The formula must result in True or False.
- The formula should reference the first row of the data range.
So, in the "AdvFilter" worksheet, delete the heading in cell A1 (or use Criteria1 as the heading), then try either one of the following formulas in cell A2:
='Track Data'!K2<>"" //OR =TRIM('Track Data'!K2)<>"" //OR =LEN('Track Data'!K2)>0Formula Criteria Example
To address the Advanced Filter results lingering afterwards, which can significantly increase the size of the file when dealing with large amounts of data, just add one line of code to clear the results (rgResults.Offset(1).ClearContents), immediately after the line of code that copies the results to an array:
'Copy the results to an array Dim rgResults As Range, n As Long Set rgResults = wsFilter.Range("A5").CurrentRegion n = rgResults.Rows.Count - 1 If n < 1 Then GoTo CleanUp Dim arr As Variant arr = rgResults.Offset(1).Resize(n).Value rgResults.Offset(1).ClearContentsIf you have any other questions, let me know. Cheers!
It is so much faster than other code I have tried and I do mean fast.
To filter 41000 rows of data in the Tracks Data worksheet, it took less that a second.
There is an interesting thing happening that I have spent the last couple of hours trying to cure but with out any success.
I set up the AdvFilter sheet exactly as you described but when I use the criteria "<>" it does not filter the results at all and just copies everything first to the AdvFilter sheet and then in to the Titles data sheet.
It does not clear the data it had copied in to AdvFilter which almost doubles the size of the Excel file.
If I was to use something that I know will appear in Column K as the filter "M+S" it filters that perfectly, the for several other text that I know will be in that column.
It just will not filter it using <> which is strange because it should.
Kenneth Green The <> operator on its own will only work if the cells in column K are truly blank. Since it's not working for you, I'm guessing the cells contain zero-length strings, most likely the result of a formula returning "". In this case, you can try using a formula as the criteria. There are 3 rules when using formulas in the criteria range:
- No heading (or use a custom heading not found in the data range, ie: "Criteria1").
- The formula must result in True or False.
- The formula should reference the first row of the data range.
So, in the "AdvFilter" worksheet, delete the heading in cell A1 (or use Criteria1 as the heading), then try either one of the following formulas in cell A2:
='Track Data'!K2<>""
//OR
=TRIM('Track Data'!K2)<>""
//OR
=LEN('Track Data'!K2)>0
Formula Criteria Example
To address the Advanced Filter results lingering afterwards, which can significantly increase the size of the file when dealing with large amounts of data, just add one line of code to clear the results (rgResults.Offset(1).ClearContents), immediately after the line of code that copies the results to an array:
'Copy the results to an array
Dim rgResults As Range, n As Long
Set rgResults = wsFilter.Range("A5").CurrentRegion
n = rgResults.Rows.Count - 1
If n < 1 Then GoTo CleanUp
Dim arr As Variant
arr = rgResults.Offset(1).Resize(n).Value
rgResults.Offset(1).ClearContents
If you have any other questions, let me know. Cheers!
Thank you everyone for all of your kind replies and I do appreciate them all.
djclements you were correct and where formulas that were returning blank, Excel had inserted an apostrophe in those cells.
I cured this by having a line of VBA that removes them.
Thank you for this tip.