Dynamic named range with blank cells

[Trish]

Hi there, this is my first time posting. I'm a newbie to VBA and I'm trying to write a macro to name ranges - dynamically because the number of rows in my data will be different each week. I think I've figured out how to do it when all the cells are populated, but I've no idea what to do when there are blanks in the column. My code to name the ranges is below and I've posted an abbreviated version of my file. Any help would be greatly appreciated! Thanks in advance.

Sub nameRanges()
Dim Rng1 As Excel.Range
'set column A & B as the ranges to be named
With ActiveSheet
Set Rng1 = .Range("A2", .Range("A2").End(xlDown))
ActiveWorkbook.Names.Add Name:="Employees", RefersTo:=Rng1
Set Rng1 = .Range("C2", .Range("C2").End(xlDown))
ActiveWorkbook.Names.Add Name:="Area", RefersTo:=Rng1
End With
End Sub

 

[NARAYANK991]

Hi Trish ,

Is there any specific reason why you wish to add dynamic named ranges using VBA ?

The standard way to create dynamic named ranges , is using the Name Manager ; the creation of the named ranges is a one-off activity , which is why the named ranges are called dynamic ; after you have created them , you can forget about them , and Excel will take over the responsibility for updating them as and when you add fresh data.

Define your Empoyees dynamic named range as follows :

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Define your Areas named ranges as follows :

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$A:$A))

Note that the Areas named range depends on the number of rows of data in the Employees named range ; this is done intentionally so that even if data exists in other columns , unless the Employee name is present , the other columns will not be taken into account.

Narayan

 

[Trish]

Thank you Narayan for responding and showing me the better way to name ranges. You have made me rethink how I will do things. I'm actually only at the very start of what I believe is probably a big ask for a newbie like me, but it gives me something very practical to work with to be able to expand my skills in Excel and develop my learning in VBA.
Each week I generate a report for casual labour and need to calculate their pays. There are twists and turns along the way, but I won't go into all the details. At the moment I use a very long process (using just Excel) to get to the final result. I'm (very slowly) trying to put together coding to do some (or all, who knows!) of the work for me. No doubt I'll be asking more questions as my project takes shape.
Thank you again.
Trish