Range without Blank values


V

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
 
Ad

Advertisements

D

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.
 
Ad

Advertisements

T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top