range select question

[Nic]

My data colums are fixed, A-J
However row changes on daily basis.

Range("A1:J11").Select
Selection.Copy

How do I replace the J11 with J variable to represent the last row,
effectively selecting the entire active range.

 

[Guest]

Are you trying to select within a macro or manually?

If manually, consider clicking in your cell block and pressing Ctrl+Shift+8
(or Ctrl+A in Excel 2003).

If you are doing it in a macro, try activating a cell in your range that
will always be in the range and selecting its CurrentRegion.
Ex:

range("A1").currentregion.copy

Or, you could modify it, so that you just have to click in the Range before
running the macro:

selection.currentregion.copy

Note that it isn't necessary to actually Select the range to copy it.

tj

 

[Don Guillett]

you do NOT need to select to copy. After getting lastrow the one liner will
do to copy your range to sheet2!a1

lastrow=cells(rows.count,"a").end(xlup).row
range("a1:j"&lastrow).copy sheets("sheet2").range("a1")