I have a 3 column, 20 row table. (A1:C20)
The 3 columns are each 1 digit of a 3 digit number.
Some of the rows have 3 digits, some are blank.
I need a way to return all the non-blank rows of 3 digits one after another starting with the top into 3 separate columns. If values are added to blank rows, the output reflects.
Ex:
A2:C2 is 4 6 2
A10:C10 is 2 6 6
A18:C18 is 0 6 1
output in D1:F3 would be
4 6 2
2 6 6
0 6 1
I have a solution as:
Sub ReturnValued ()
Set cpySht = Sheets("Sheet1")
Set pstSht = Sheets("Sheet2")
i = 0
For Each cell In cpySht.Range(cpySht.Cells(1, "A"), cpySht.Cells(cpySht.Cells(Rows.Count, "A").End(xlUp).Row, "A"))
If cell.Value <> "" Then
i = i + 1
pstSht.Cells(i, "A") = cell.Value
End If
Next cell
End Sub
I was just wondering if there was a better solution or one with formulas.
Any help is appreciated! Formulas preferred, macros welcome.