named range in macro call

[hjc]

Is there a way to pass a named range (or even an unnamed block of cells) into
a macro?

I have written a macro that I want to use for several different ranges of
data. I have created multiple buttons that each call the macro, but I cannot
seem to find the right syntax to pass in the range of data that I want the
macro to use for each button. All I can do is to pass in the row and column
numbers that define the upper left and lower right corners of the data range,
as follows:

Private Sub CommandButton1_Click()
' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol)
Call Macro1(16, 9, 40, 35)
End Sub

Private Sub CommandButton2_Click()
Call Macro1(51, 9, 75, 35)
End Sub

etc.

If possible, I would like to be able to specify named ranges instead, so
that I don't have to edit the code for all the buttons if I add, delete or
move rows or columns. Is there a way to do this?

Thanks!
hjc

 

[Jim Thomlinson]

try something like this...

public sub macro1(rng as range)
msgbox rng.address
end sub

Sub test
Call macro1(thisworkbook.names("MyRange").referstorange)
end sub

 

[Dave Peterson]

Sub Macro1(myRng as range)
msgbox myrng.address(external:=true)
End Sub

And you can call it like:

Dim Rng as range
set rng = workbooks("Some workbook.xls").worksheets("sheet99").range("a1:b9")
Call macro1(myRng:=rng)

 

[JLatham]

This might also help along with what Jim Thomlinson offered.

This code snippet can be used to come up with the row and column numbers for
the upper left corner and lower right corner of a named range, just
substitute the actual name of the range where I show "NamedRangeName" in the
snippet


Dim UpperLeftRow As Long
Dim UpperLeftCol As Long
Dim LowerRightRow As Long
Dim LowerRightCol As Long
Dim rangeAddress As String

rangeAddress = ThisWorkbook.Names("NamedRangeName").RefersToR1C1
rangeAddress = Right(rangeAddress, Len(rangeAddress) - _
InStr(rangeAddress, "!"))
UpperLeftRow = Val(Mid(rangeAddress, 2, _
InStr(rangeAddress, "C") - 2))
UpperLeftCol = Val(Mid(rangeAddress, _
InStr(rangeAddress, "C") + 1, InStr(rangeAddress, ":") - _
InStr(rangeAddress, "C") + 1))
rangeAddress = Right(rangeAddress, Len(rangeAddress) - _
InStr(rangeAddress, ":"))
LowerRightRow = Val(Mid(rangeAddress, 2, _
InStr(rangeAddress, "C") - 2))
LowerRightCol = Val(Right(rangeAddress, _
Len(rangeAddress) - InStr(rangeAddress, "C")))
'then you could call your macro as:
Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol)