copy data from one worksheet and disregard blank cells

[Allan]

I have data on one worksheet that needs to be copied onto another. The new
worksheet must have this data however it must diregard the blank cells.
I cannot sort and then delete as each cell contains a formula that
references another worksheet.
a1 = abc
a2 = def
a3 = blank cell
a4 = fgh
a5 = ijk

Many Thanks
Allan
new worksheet should read as
a1 = abc
a2 = def
a3 = fgh
a4 = ijk
there is no longer a blank cell and the total rows has been reduced by the
number of blank cells the original worksheet contained.

 

[Mike H]

Allan,

Select the top cell of your range then

Data|Filter|AutoFilter

On the Autofilter dropdown select 'Non Blanks'
Select all your data and Copy
paste it into the new location and the blank rows are gone

Mike

 

[Allan]

Thanks Mike,
Such a simple solution, that's probably why I couldn't findit.

Thanks Again

 

[Stefi]

1. Select column A
2. Data>Autofilter, choose non-blank cells from dropdown list
3. Edit>Jump (I'm not sure what is the original English word, it's the
Ctrl+G shortcut, please re-post it if you Found it), Advanced (this is the
push button in the bottom left corner of the dialog, please re-post its name,
too), choose the Visible cells only option
4. Copy and paste

Regards,
Stefi


„Allan†ezt írta:

 

[Mike H]

Glad I could help

Thanks Mike,
Such a simple solution, that's probably why I couldn't findit.

Thanks Again

 

[ShaneDevenshire]

Hi Stefi,

The name of the command in English is Edit, Go To. But not to worry, I have
not idea what it is in French or German or anything else.

By the way the shortcut key for this command is either Ctrl+G or F5.

Also, and maybe more importantly, you don't need to select visible cells
when you copy a filtered list, because Excel will automatically copy only the
visible cells, this can save you a few steps.

However, if you are copying from a range than has Data, Subtotal applied and
rows hidden you DO need to use your command of Go To, Special, Visible cells.

 

[Stefi]

Thanks Shane for making clear the difference between cases of handling hidden
rows and also for the linguistic support.

Regards,
Stefi

„ShaneDevenshire†ezt írta: