Range naming cells with blank cells through coding

[Naveen J V]

Hi,

Through macro, how do I name a range which consists of blank cells in
between. The range will be derived dynamically, where only the starting cell
is known. For example, Range starts frm A5, and end cell should be selected
dynamically based on the data. In between the dynamic range there may be some
blank cells. How to I code?

Thanks in advance

 

[Gary''s Student]

The following will create a Named range of the empty cells below A5 until a
non-empty cell is found:

Sub servient()
Set a5 = Range("A5")
s = "=Sheet1!$A$6:$A$" & a5.End(xlDown).Row - 1
ActiveWorkbook.Names.Add Name:="bigji", RefersTo:=s
End Sub