copy data from one worksheet and disregard blank cells
 

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.

copy data from one worksheet and disregard blank cells
 

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" wrote:

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.

copy data from one worksheet and disregard blank cells
 

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

Thanks Again


"Mike H" wrote:

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" wrote:

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.

copy data from one worksheet and disregard blank cells
 

1. Select column A
2. DataAutofilter, choose non-blank cells from dropdown list
3. EditJump (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:

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.

copy data from one worksheet and disregard blank cells
 

Glad I could help

"Allan" wrote:

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

Thanks Again


"Mike H" wrote:

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" wrote:

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.

copy data from one worksheet and disregard blank cells
 

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.

--
Thanks,
Shane Devenshire


"Stefi" wrote:

1. Select column A
2. DataAutofilter, choose non-blank cells from dropdown list
3. EditJump (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:

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.

copy data from one worksheet and disregard blank cells
 

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

Regards,
Stefi

€žShaneDevenshire€� ezt Ã*rta:

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.

--
Thanks,
Shane Devenshire


"Stefi" wrote:

1. Select column A
2. DataAutofilter, choose non-blank cells from dropdown list
3. EditJump (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:

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.