pasting creates 65000 lines

G

Guest

My macro filters for data, then select all all cells(ctrl+a) and then pastes
in a new sheet which works fine but it makes the file size hugh and creates
65000 rows, how do i copy and paste without doing this without having to know
before hand how many rows and columns to select. This also applies when it's
not a macro and i just filter, copy and paste into a new sheet.
 
J

Jim Rech

Ctrl-a selects the entire sheet (Except in Excel 2003). It would be better
to select just the region with data. How you do that depends on how the
data is arranged.

This select all data:

ActiveSheet.UsedRange.Select

This selects the contiguous region of data around the active cell (same as
pressing Ctrl-*):

ActiveCell.CurrentRegion.Select

--
Jim Rech
Excel MVP
| My macro filters for data, then select all all cells(ctrl+a) and then
pastes
| in a new sheet which works fine but it makes the file size hugh and
creates
| 65000 rows, how do i copy and paste without doing this without having to
know
| before hand how many rows and columns to select. This also applies when
it's
| not a macro and i just filter, copy and paste into a new sheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

remove excess (unwanted) rows from the bottom of a speadsheet 3
Pivot from all sheets 1
Copying data to the next blank row 0
Filter-Copy-Paste Problem 3
Macro to copy value in field and insert it into filter 2
How to copy a range of cells and paste into a new workbook in differentcolumns 3
excel macro-copy formula to entire column? 2
Copy and pasting values of filtered rows 1

Top