Need vba code to select only cells with data

[ybortony]

I have created a macro to copy subsets of data to a new workbook by filtering the main set of data. It would appreciated if someone would provide me with code to select only the cells with data(in the new workbook) so I can copy that data to another sheet. Currently the copy procedure copies all the rows in the sheet, including the blank rows which recreates a huge file. Thank you in advance for any assistance you may provide.

[JoePublic]

Are the blank rows within the data or beneath it?

[Simi]

you could have the macro go line by line and copy the data only if cell A? has data in it.
This kind of code will take a long time to execute.

Or you could sort the master file to get all the blank rows at the bottom of the range.
then recalculate how many rows are being used.
then copy your entire range at once.

Simi

[ybortony]

sorry for the delayed response. I appreciate your posts. the data is contiguous from A1 to last col and last row.

[Simi]

I have needed this same kind of thing several times.


'

'initalize variables 
   Dim lRows As Long 
   Dim lCols as Long 
           
'calculate last row and last column
   With ActiveSheet 
             lRows = .Cells(.Rows.Count, "A").End(xlUp).Row 
             lCols = .Cells(1, .Columns.Count).End(xlToLeft).Column 
           
             'copy data 
             .Range(cells(1,1), cells(lRows, lCols)).Copy 
     End With

Hope this helps

sorry the formatting sucks.

[royUK]

Try

Sheet1.Cells(1, 1).CurrentRegion.Offset(1).Copy Sheet2.Cells(1, 1)

Offset prevents a header row being copied