Hi Denys,

Am Wed, 15 Apr 2015 07:40:36 -0700 (PDT) schrieb :

In my column H, I have three possibilities: Project, Support or Group. Is is possible with VBA to name a range of cells according to the data entered in those cells ?

In other words, I need to have three range of cells (that will change every day)

sort your data by column Hand try following code:

Sub Test()
Dim strData As String
Dim varData As Variant
Dim i As Long

strData = "Project,Group,Support"
varData = Split(strData, ",")

For i = 0 To UBound(varData)
ActiveSheet.Names.Add Name:=varData(i), RefersTo:= _
"=OFFSET($H$1,MATCH(""" & varData(i) & _
""",$H$1:$H$1000,0)-1,,COUNTIF($H:$H,""" & varData(i) & """))"
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional