Name Range

[Abdul]

The following is a modified code from this newsgriou

Sub MonthRange()
Dim iStart As Long
Dim iEnd As Long
Dim rng As Range
Dim i As Long
ActiveWorkbook.Names.Add Name:="DlyAll", RefersToR1C1:= _
"=OFFSET(Daily!R1C1,1,0,COUNTA(Daily!C1)-1)"
With Sheets("Daily")
For i = 1 To 12
iStart = _
.Evaluate("=MIN(IF(MONTH(DlyAll)=" & i &
",ROW(DlyAll)))")
iEnd = _
.Evaluate("=MAX(IF(MONTH(DlyAll)=" & i &
",ROW(DlyAll)))")
If iEnd <> 0 Then
Set rng = Sheets("Daily").Range("A" & iStart & ":A" & iEnd)
rng.Name = "Rng" & Format(DateValue("01-" & i), "mmm")
End If
Next i
End With

if I want to get the range named based on month and year from data of
more than one year how i can modify this code?

so the Range name will look like RngJan06 for January 2006 Data and
RngJan2007 for 2007 Data?

thanks

Abdul

 

[Bob Phillips]

rng.Name = "Rng" & Format(DateValue("01-" & i), "mmmyy")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Abdul]

Thanks Bob,

It will just rename it

How I can have two range names for two different years?

for eg: if have data starting from 1/1/2005 to 13/1/2007

Then I want to get range name like RngJan05, RngFeb05....RngJan06,
RngFeb06,...RngJan07
So i want to evaluate both month and year. Number of years will be
based on year from the smallest date and year from the highest date
from the whole data

Thanks

 

[Bob Phillips]

Sub MonthRange()
Dim iStart As Long
Dim iEnd As Long
Dim rng As Range
Dim i As Long
Dim j As Long

ActiveWorkbook.Names.Add Name:="DlyAll", RefersToR1C1:= _
"=OFFSET(Daily!R1C1,1,0,COUNTA(Daily!C1)-1)"
With Sheets("Daily")
For j = .Evaluate("MIN(YEAR(DlyAll))") To _
.Evaluate("MAX(YEAR(DlyAll))")
For i = 1 To 12
iStart = _
.Evaluate("=MIN(IF((MONTH(DlyAll)=" & i & ")*" & _
"(YEAR(DlyAll)=" & j & "),ROW(DlyAll)))")
iEnd = _
.Evaluate("=MAX(IF((MONTH(DlyAll)=" & i & ")*" & _
"(YEAR(DlyAll)=" & j & "),ROW(DlyAll)))")
If iEnd <> 0 Then
Set rng = Sheets("Daily").Range("A" & iStart & _
":A" & iEnd)
rng.Name = "Rng" & Format(DateValue( _
"01-" & i & "-" & j), "mmmyy")
End If
Next i
Next j
End With

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Abdul]

Thanks Bob!

It worked great!

Abdul