VBA code for selecting and copying rows to another sheet.

  • Thread starter Thread starter lothario
  • Start date Start date
L

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
 
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
 
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?
 
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
 
Back
Top