Range without Blank values

[vvaidya]

Hello:
i would appreciate help with the following
I have a range (A1:A10) on Sheet1, with names of people. Some of the
cells in this range are blank.
On Sheet2, I want to populate the column A (from A1 downward) with the
list of names from Sheet1, range (A1:A10) but without any blank cells.

Thus for example if on Sheet1, in range A1 to A10 if only cells, A3, A8
& A10 have names & other cells in range A1:A10 are blank, then on
Sheet2, I would like cells A1, A2, A3 to have the values of A3, A8 &
A10 from sheet1

TIA

Vinay

 

[Dave Peterson]

Are these values or formulas or a mixture?

If they are not a mixture, you can:
select A1:A10
edit|goto|special|check constants (or formulas)
Edit|copy
then go to A1 of the second sheet
edit|paste


If you need a macro, you can record one when you do it manually.

 

[Tom Ogilvy]

assuming the names have been entered a constants and are not the product of
formulas

Sub CopyData()
Dim rng as Range

On Error Resume Next
With Worksheets("Sheet1")
set rng = .Range("A1:A10").SpecialCells(xlConstants,xlTextValues)
End with
On Error goto 0
if not rng is nothing then
With Worksheets("Sheet2")
rng.copy .Range("A1")
End With
end if
End Sub