Name Range
 

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

Name Range
 

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


--
HTH

Bob Phillips

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

"Abdul" wrote in message
oups.com...
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

Name Range
 

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 wrote:
rng.Name = "Rng" & Format(DateValue("01-" & i), "mmmyy")


--
HTH

Bob Phillips

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

"Abdul" wrote in message
oups.com...
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

Name Range
 

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" wrote in message
oups.com...
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 wrote:
rng.Name = "Rng" & Format(DateValue("01-" & i), "mmmyy")


--
HTH

Bob Phillips

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

"Abdul" wrote in message
oups.com...
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

Name Range
 

Thanks Bob!

It worked great!

Abdul