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

Selecting all cells using macro
 

something like this??

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

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

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