I am trying to a use array to copy all rows to a new sheet if a cell is not blank.
I know there are other methods to do this but was trying to get a better hand with arrays but am failing here
Right now the code copies the header row multiple times and moves all the content
Thanks for any help or insight
Sub MoveRowIfCellIsNotEmpty()
Dim myArray() As Variant
Dim tempArray() As Variant
Dim Destination As Range
Dim wsS As Worksheet
Dim wsT As Worksheet
Dim LC As Long
Dim LR As Long
Dim i As Long
Dim j As Long
Set wsS = ThisWorkbook.Sheets("NodeFile")
Set wsT = ThisWorkbook.Sheets("XXX")
With wsS
LC = (.Cells(1, .Columns.Count).End(xlToLeft).Column)
LR = (.Range("A" & .Rows.Count).End(xlUp).Row)
myArray = .UsedRange
End With
j = 1
For i = 1 To LR - 1
If myArray(i, 4) = "" Then 'if cell is NOT blank paste ROW to new sheet
Set Destination = wsT.Range("A" & j)
Destination.Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray
j = j + 1
End If
Next
End Sub
Bookmarks