Passing arguments with worksheet_change to dynamically update ranges

[Prevost]

Hi There. I have found a sub that will dynamically update named ranges in worksheets. There are two things that I would like to know if possible. First, I would like to know if there is a way to disregard blank cells in that range (starting at A3 that goes to the last row). I am using that range for a combobox and would prefer it if there were not any blank selections in the list. Second, I have multiple ranges and I do not want to call this sub everytime, so is there a way to create this macro with variables to insert? I am still not 100% clear on passing arguments and am trying to learn more. I thought something like

Private Sub worksheet_change(ByVal target as Range, Column as String, FirstCell as String, RangeName as String)

and then filling in the values for Column, the starting cell and the name of the range....

Thanks!

Private Sub worksheet_change(ByVal target As Range)
If Intersect(target, Columns("A:A")) Is Nothing Then Exit Sub
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A3:A" & lRow).Name = "MyRange"
End Sub

 

[Norie]

That's right you can't pass your own arguments to any of the built-in events.

If you try you'll get a compile error, the text of which escapes me right now.

 

[Prevost]

Ok then. Thanks for the replies. I will have to rethink my approach.