Function

[Todd Huttenstine]

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

 

[anilsolipuram]

go to tools->macro->visual basic editor


type in the editor


sub test()
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
end sub


and to execute it go to tools->macro->macros, you will see tes
procedure listed there to run

 

[Don Guillett]

Functions ( I assume you mean a UDF) return value as a regular function.
However, you code could be simplified and withOUT selections

with worksheets("Agents")
lr=.cells(rows.count,"a").end(xlup).row
'======
lc=.cells(columns.count,"b").end(xltoleft).column
'or
'lc=activecell.column
'========
..range(cells(2,1),cells(lr,lc).copy _
sheets("destinationsheet").range("a1")
end with
===========

 

[JE McGimpsey]

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).

 

[Don Guillett]

I forgot a ). Should test.
..range(cells(2,1),cells(lr,lc)).copy

 

[JE McGimpsey]

You also forgot that Cells should have the prepended dot...

.range(.cells(2,1),.cells(lr,lc)).copy _

 

[Don Guillett]

Thanks JE
haste and failure to test make waste.

 

[JE McGimpsey]

Yup. But if I had a nickel for every time *I've* made that particular
error...