Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
select all non-blank cells
I need a macro to select and copy all non-blank cells in Sheet1, then paste
these cells into sheet2 at Cell A1. thanks much! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
select all non-blank cells
Try the below
Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") End Sub 'If you have any formulas that are to be converted to values then try this version Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value End Sub -- Jacob (MVP - Excel) "johncaleb" wrote: I need a macro to select and copy all non-blank cells in Sheet1, then paste these cells into sheet2 at Cell A1. thanks much! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
select all non-blank cells
Jacob
You seem to be assuming the used range is a block of contiguous cells only If blanks are interspersed throughout the usedrange they will be copied also. OP might as well just copy sheet1 to sheet2 Gord Dibben MS Excel MVP On Thu, 6 May 2010 08:31:02 -0700, Jacob Skaria wrote: Try the below Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") End Sub 'If you have any formulas that are to be converted to values then try this version Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
select all non-blank cells
Yes you are right. Not sure whether the OP meant to avoid blank
rows...eventhough its is mentioned as 'non-blank cells' -- Jacob (MVP - Excel) "Gord Dibben" wrote: Jacob You seem to be assuming the used range is a block of contiguous cells only If blanks are interspersed throughout the usedrange they will be copied also. OP might as well just copy sheet1 to sheet2 Gord Dibben MS Excel MVP On Thu, 6 May 2010 08:31:02 -0700, Jacob Skaria wrote: Try the below Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") End Sub 'If you have any formulas that are to be converted to values then try this version Sub Macro() Sheets("Sheet1").UsedRange.Copy Sheets("Sheet2").Range("A1") Sheets("Sheet2").UsedRange = Sheets("Sheet2").UsedRange.Value End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to select blank cells only whilst using sumproduct | Excel Worksheet Functions | |||
Up down arrow keys do not select cells if select locked cells unch | Excel Discussion (Misc queries) | |||
Can Excel automatically select print area on non-blank cells? | Excel Discussion (Misc queries) | |||
How To Select Blank Cells within a worksheet | Excel Discussion (Misc queries) | |||
Select NON blank cells | New Users to Excel |