0

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.

2
  • Values have to be non-blank for ALL three rows?
    – datatoo
    Aug 29, 2014 at 19:23
  • The table values are not going to have 1 column with a value without the other two. They're always 3 digit numbers.
    – Will S
    Aug 29, 2014 at 19:24

1 Answer 1

0
Sub returnValues()

'last row used in the origin data sheet
lastRow = Sheets("sheet1").UsedRange.Rows.Count

'counter for the rows to be pasted in sheet 2
n = 1

'loop through all rows
For rowNum = 1 To lastRow
    'test if all three cells in the row have data in them:
    If Not IsEmpty(Sheets("sheet1").Cells(rowNum, 1)) And _
        Not IsEmpty(Sheets("sheet1").Cells(rowNum, 2)) And _
        Not IsEmpty(Sheets("sheet1").Cells(rowNum, 3)) Then
        'if ok, copies the entire row and paste to sheet 2
        Sheets("sheet1").Cells(rowNum, 1).EntireRow.Copy Sheets("sheet2").Range("A" & n)
        n = n + 1
    End If
Next rowNum

End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .