| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy cell value into blank cells below (full worksheet) | Excel Worksheet Functions | |||
| CSE formula disregard blank cells | Excel Worksheet Functions | |||
| Copy Data from Row 1 to last blank row in next worksheet | Excel Worksheet Functions | |||
| How do i make cells in one worksheet copy certain data to another | Excel Worksheet Functions | |||
| Copy rows of data to another worksheet where ReturnDate is blank | Excel Discussion (Misc queries) | |||
Linear Mode
