Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting all cells using macro
I need to be able to select a range of cells from the first (A1) to the last
active cell. I know there is a Go To.... / Special Cells option but I need a statement to do this that I can use in a macro. If possible I would like to give this a name (eg-"All_Active_Cells" so that I can reference it easily in the macro. Thanks Robert |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting all cells using macro
something like this??
Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting all cells using macro
You could employ the usedrange selection.
Sub select_range() ''good if used range has been previously reset Dim someCells As Range With ActiveSheet.UsedRange Range("A1").Select Set someCells = ActiveSheet.Range(ActiveCell, _ .Cells(.Cells.Count)) End With someCells.Select End Sub But Excel has a habit of over-estimating the usedrange so another method can be employed to circumvent that over-estimation. Add this UDF to your workbook. Function RangeToUse(anySheet As Worksheet) As Range 'Bob Flanagan construct. 'this function returns the range from cells A1 to cell which is the 'intersection of the last row with an entry and the last column with an entry. 'used with UsedRangePick macro.....REAL USED RANGE!! Dim i As Integer, C As Integer, r As Integer With anySheet.UsedRange i = .Cells(.Cells.Count).Column + 1 For C = i To 1 Step -1 If Application.CountA(anySheet.Columns(C)) 0 _ Then Exit For Next i = .Cells(.Cells.Count).Row + 1 For r = i To 1 Step -1 If Application.CountA(anySheet.Rows(r)) 0 Then _ Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(r, C)) End With End Function Then use this macro. Sub UsedRangePick() Dim tempRange As Range Set tempRange = RangeToUse(ActiveSheet) tempRange.Select End Sub Gord Dibben MS Excel MVP On Sat, 7 Oct 2006 14:03:02 -0700, robertlewis wrote: I need to be able to select a range of cells from the first (A1) to the last active cell. I know there is a Go To.... / Special Cells option but I need a statement to do this that I can use in a macro. If possible I would like to give this a name (eg-"All_Active_Cells" so that I can reference it easily in the macro. Thanks Robert |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting all cells using macro
Sub setnamerange()
'last row in col A lr = Cells(Rows.Count, "a").End(xlUp).Row Set myrng = Range(Cells(1, "a"), Cells(lr, "a")) myrng.copy 'Select End Sub -- Don Guillett SalesAid Software "robertlewis" wrote in message ... I need to be able to select a range of cells from the first (A1) to the last active cell. I know there is a Go To.... / Special Cells option but I need a statement to do this that I can use in a macro. If possible I would like to give this a name (eg-"All_Active_Cells" so that I can reference it easily in the macro. Thanks Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use macro to edit data in Excel cells | Excel Discussion (Misc queries) | |||
selecting cells which contain formulas but return blanks | Excel Discussion (Misc queries) | |||
Macro, Copy Selected Cells Down a Column | Excel Discussion (Misc queries) | |||
Copy Selected cells down a row with macro | Excel Discussion (Misc queries) | |||
Macro to delete data in 'green' cells only | Excel Worksheet Functions |