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