Dim rng as Range, rng1 as Range
Dim i as Long
With Worksheets("SheetSource")
for i = 1 to 4
set rng = .Range(.Cells(1,i),.Cells(rows.count,i).End(xlup))
set rng1 = rng.SpecialCells(xlBlanks)
rng.EntireRow.Hidden = True
rng.Copy Destination:=Worksheets("SheetDest").Cells(1,i)
rng1.EntireRow.Hidden = False
Next i
End with

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Thanks Tom for your reply

Problem with this code is that I would have to hard type all ranges in -
I'm
likely to have approx 150 'ranges' which I will have to paste

Is it possible to specify a 'Start' cell in each column and then Jump down
22 cells to the next 'source cell, continue this in the same column until
there are no values left,then move to Column B and do the same etc?




"Tom Ogilvy" wrote in message
...
Sub Tester3()
varr = Evaluate("{""A1"",""A1"";""B1"",""B1"";" & _
"""C1"",""I20"";""D1"",""J20"";" & _
"""A2"",""A23"";""B2"",""B23"";" & _
"""C2"",""I42"";""D2"",""J42""}")
For i = LBound(varr, 1) To UBound(varr, 1)
Worksheets("Sheet2").Range(varr(i, UBound(varr, 2))).Value = _
Worksheets("Sheet1").Range(varr(i, LBound(varr, 2))).Value
Next
End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
I have a list of values in cells that I want to paste to a new
worksheet,
however these values are not on either the same Row or column, but I
want
the 'output' worksheet to be in the format

A1 = Value1; B1 = Value2; C1 = Value3; D1 = Value4
A2 = Value5; B1 = Value6; C1 = Value7; D1 = Value8

The layout of the above values in the 'source' worksheet is as follows

A1 = Value1; B1 = Value2; I20 = Value3; J20 = Value4
A23 = Value5; B23 = Value6; I42 = Value7; J42 = Value8

As you can see there is a set gap in rows between each of my
'segments'
of
data. Values to 'output' worksheet should be pastespecial values, as
the
source are formulated.

Thanks