Are the blank rows within the data or beneath it?
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.
Are the blank rows within the data or beneath it?
This website wants to know your momentum - | Deny | | Allow |
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
sorry for the delayed response. I appreciate your posts. the data is contiguous from A1 to last col and last row.
I have needed this same kind of thing several times.
'Code:'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.
Last edited by Simi; 2013-04-26 at 12:39 AM. Reason: formatting code problems
Try
Offset prevents a header row being copiedCode:Sheet1.Cells(1, 1).CurrentRegion.Offset(1).Copy Sheet2.Cells(1, 1)
Hope that helps
Roy
Bookmarks