Function

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

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
 
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
 
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
===========
 
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).
 
You also forgot that Cells should have the prepended dot...

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