select all non-blank cells

J

johncaleb

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!
 
J

Jacob Skaria

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
 
G

Gord Dibben

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
 
J

Jacob Skaria

Yes you are right. Not sure whether the OP meant to avoid blank
rows...eventhough its is mentioned as 'non-blank cells'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select all non-blank cells from different workbook 1
Need Help with a VBA subroutine 0
Select Text to End of Footnote 9
Copy addition formula of non-consecutive horizontal cells 2
Pasting a cell into bookmarked table from excel vba 1
Exporting Outlook 2007 contact list without losing groups 0
Copy All Visible Cell Of Sheet1 To Sheet2 Last Blank Row 2
copy and paste from workbook to workbook 3

Top