Have been trying to sort this for a while so any help appreciated.
the below code works great for looping through the range and adding a single cell to an array before looping and adding another single cell etc and finally pasting the array to another sheet.
What I want to do is rather than add a single cell I want to add the cell and the adjacent cell to the array before continuing the loop and then pasting to the other sheet.
I know I could run the array twice and just change the column references but can someone explain how i expand the array to do it in 1 go
The array would take data from say col "C:D" from arrow before continuing the loop through the rows.
the below code works great for looping through the range and adding a single cell to an array before looping and adding another single cell etc and finally pasting the array to another sheet.
What I want to do is rather than add a single cell I want to add the cell and the adjacent cell to the array before continuing the loop and then pasting to the other sheet.
I know I could run the array twice and just change the column references but can someone explain how i expand the array to do it in 1 go
The array would take data from say col "C:D" from arrow before continuing the loop through the rows.
VBA Code:
For Each cel In ws1.Range(ws1.Cells(TPS1, Mycol), ws1.Cells(TPS2 - 3, Mycol))
If cel <> "" Then
If cel = "A" Or cel = "B" Or cel = "C" Or cel = "D" Then
ReDim Preserve MySupv(1 To x)
MySupv(x) = ws1.Cells(cel.Row, Mycol - 3)
x = x + 1
Else
'do something else if criteria not met
End If
Else
End If
Next cel
ws11.Cells(1, 1).Resize(x - 1) = Application.Transpose(MySupv)