selecting a range to copy paste from one sheet to another

bill151515

Board Regular
Joined
May 19, 2005
Messages
87
how do you select the range of the last row with data in it and the last column with a cell of data in it. i want to avoid using cells.select data in and just select the area that has data instead of the whole sheet
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
Assuming your data starts in cell A1 - -

Sub Test1()
Dim LR&, LC%
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(Cells(1, 1), Cells(LR, LC)).Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,033
Messages
5,569,777
Members
412,291
Latest member
marypolitan
Top