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!
To copy the rows with specific columns in a specific order, you can modify the code to copy and paste the columns in the desired sequence. Here is the adjusted code:
Vba Code is untested, please backup your file first.
Option Explicit
Sub CopyRowsWithData()
Dim erow As Long, lastrow As Long, i As Long
Dim RngCopy As Range
Dim wsTrackData As Worksheet
Dim wsTitlesData As Worksheet
Application.CutCopyMode = True
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableAnimations = False
End With
' Set references to the worksheets
Set wsTrackData = ThisWorkbook.Worksheets("Track Data")
Set wsTitlesData = ThisWorkbook.Worksheets("Titles Data")
With wsTrackData
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Trim(.Cells(i, "K").Value) <> "" Then
' Set the range to copy in the desired order
Set RngCopy = .Range("A" & i) ' Column A
Set RngCopy = Union(RngCopy, .Range("B" & i)) ' Column B
Set RngCopy = Union(RngCopy, .Range("I" & i)) ' Column I
Set RngCopy = Union(RngCopy, .Range("J" & i)) ' Column J
Set RngCopy = Union(RngCopy, .Range("K" & i)) ' Column K
Set RngCopy = Union(RngCopy, .Range("F" & i)) ' Column F
Set RngCopy = Union(RngCopy, .Range("G" & i)) ' Column G
Set RngCopy = Union(RngCopy, .Range("H" & i)) ' Column H
RngCopy.Copy ' Copy the Union range
' Get the next empty row in "Titles Data"
erow = wsTitlesData.Cells(wsTitlesData.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
' Paste in the next empty row, starting from column A
wsTitlesData.Cells(erow, 1).PasteSpecial xlPasteAll
End If
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableAnimations = True
End With
Application.CutCopyMode = False
End SubThis code snippet ensures that the columns are copied and pasted in the desired order specified in your example. Adjustments were made to the range assignment and the paste location to align with the desired column order in the "Titles Data" worksheet. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Thank you for your suggestion but it just copies the data in the same column order as the source sheet.
I actually tried setting the target order in much the same way in my code example but that does not work.
As a side note, I find that AI generated VBA code does not work as there are too many things to be considered and some forums do not allow the use of AI when answering questions.
Can't beat real world experience of experts.