one way:

Public Function CopyRange()
With Worksheets("Agents")
.Range(.Cells(2, 1), _
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
.Cells(1, 2).End(xlToRight).Column)).Copy
End With
End Function

note however, that this function won't do anything if called from the
worksheet (as functions can only return values to cells).

In article ,
"Todd Huttenstine" wrote:

The below code selects a range of cells. Using the below
method, how do I make this code into a function?


Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy



Thanks
Todd Huttenstine