copy cells in a range to a column

Question

i have MyRange = A1:C3

current values in the range are as below:

A1=a, B1=d, C1=f A2=b, B2=e, C2="" A3=c, B3="", C3=""

the blank cells in the range can vary.

how can i copy (using vba) non-blank values from MyRange and paste them all together let's say to column AA?

Eg: AA1=a AA2=b AA3=c AA4=d AA5=e AA6=f

Thanks again guys :-) Paul

Answer 1

Iterate through all cells in MyRange, if cell is not "" copy value to next target cell

Sub test()
    Dim MyRange as Range
    Dim TargetCell as Range
    Dim rw as Range
    Dime cl as Range

    Set MyRange = ActiveWorkbook.Names("MyRange").RefersToRange
    Set TargetCell = Range("AA1") 
    For each rw in MyRange.Columns
        For each cl in rw.Cells
            If cl.value <> "" then
                TargetCell = cl.value
                Set TargetCell = TargetCell.Offset(1,0)
            End If
        Next
    Next

End Sub