Select Data only

  • Thread starter Thread starter universal
  • Start date Start date
U

universal

Hello All,

I wonder if you can help with this (probably simple) problem.


Each worksheet Im working on may have several hundred rows, though each
one a different number of rows.
Im compiling them all to one worksheet.

The columns used, however, are set. So I'm trying to make a macro to
Select all the columns but only as far down as the data goes.

I'll need a similar (probably) macro to search down in the "Combined"
worksheet, find the first free row and ValuePaste the data in there.

Ive got as far as realising it may be something to do with:

Range("A:M").Select
Range(Selection, Selection.End(xlDown)).Select

But thats as far as Ive got.

Any help much appreciated.

Thanks
Ed
 
One way:

If column A will have a value in each record, then this will copy
all the records on sheet1 to the next available row on sheet2:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
End With

This technique has XL look "up" from the bottom row to find the last
row with data.

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.
 
I think J.E.'s code got truncated somehow:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
End With

Might be what he really meant.
 
Back
Top