Hi,

I have 39,000 rows of data in one spreadsheet.
I need some VBA code that will copy ONLY the rows where column A is
non-blank.
I want these few rows to be copied to another sheet in the same
workbook.
At best, there will be only about 30 rows that are non-blank in column
A.

Can you please give me the VBA code for doing this?

I have tried to use PivotTables. It does not work. It keeps saying
that I do not have enough memory.

Thanks,
Luther



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I'm assuming that you're not interested in copying non-blanks from
Column A that contain formulas. If so, you'll have to modify the
macro.

Sub NoBlanks()
Columns("A").SpecialCells(xlCellTypeConstants).Ent ireRow.Copy
Sheets("OtherSheet").Range("A1").PasteSpecial xlPasteValues
End Sub

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hi,

I have 39,000 rows of data in one spreadsheet.
I need some VBA code that will copy ONLY the rows where column A is
non-blank.
I want these few rows to be copied to another sheet in the same
workbook.
At best, there will be only about 30 rows that are non-blank in column
A.

Can you please give me the VBA code for doing this?

I have tried to use PivotTables. It does not work. It keeps saying
that I do not have enough memory.

Thanks,
Luther

Thanks. This is what I needed.

Can you tell me what needs to be changed to copy only columns 2 thru 6
in every row instead of the entire row?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sub CopyData()
Dim rng As Range
Set rng = Columns("A").SpecialCells(xlCellTypeConstants).Ent ireRow
Set rng = Intersect(rng, Range("B:F"))
rng.Copy
Sheets("OtherSheet").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

--
Regards,
Tom Ogilvy



"lothario" wrote in message
...
Thanks. This is what I needed.

Can you tell me what needs to be changed to copy only columns 2 thru 6
in every row instead of the entire row?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Thanks.
This helped.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/