VBA code for selecting and copying rows to another sheet.

[lothario]

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

 

[pfsardella]

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).EntireRow.Copy
Sheets("OtherSheet").Range("A1").PasteSpecial xlPasteValues
End Sub

HTH
Paul

 

[lothario]

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?

 

[Tom Ogilvy]

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

 

[lothario]

Thanks.
This helped.