Simplify Code

Hello,

I have the following code to define a range based on month which is
sorted date wise.

is there a way to avoid repeating the code 12 times for all months?

also if there is no dates for a specific month say for november to
avoid causing an error while selecting the range since the range will
be empty?

Thanks


Dim iStart As Long
Dim iEnd As Long
Dim Rng As Range

iStart = _
Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
iEnd =
Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
Set Rng = Range("A" & iStart & ":A" & iEnd)
Rng.Name = "rngJan"

iStart = _
Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
iEnd =
Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
Set Rng = Range("A" & iStart & ":A" & iEnd)
Rng.Name = "rngFeb"

iStart = _
Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
iEnd =
Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
Set Rng = Range("A" & iStart & ":A" & iEnd)
Rng.Name = "rngMar"

Dim iStart As Long
Dim iEnd As Long
Dim Rng As Range
Dim i As Long

With Sheets("Daily")
For i = 1 To 12
iStart = _
.Evaluate("=MIN(IF(MONTH(DlyAll)=" & i & ",ROW(DlyAll)))")
iEnd = _
.Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
Set Rng = Range("A" & iStart & ":A" & iEnd)
Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm")
Next i
End With


--
HTH

Bob Phillips

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

"Soniya" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have the following code to define a range based on month which is
> sorted date wise.
>
> is there a way to avoid repeating the code 12 times for all months?
>
> also if there is no dates for a specific month say for november to
> avoid causing an error while selecting the range since the range will
> be empty?
>
> Thanks
>
>
> Dim iStart As Long
> Dim iEnd As Long
> Dim Rng As Range
>
> iStart = _
> Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> iEnd =
> Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> Set Rng = Range("A" & iStart & ":A" & iEnd)
> Rng.Name = "rngJan"
>
> iStart = _
> Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> iEnd =
> Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> Set Rng = Range("A" & iStart & ":A" & iEnd)
> Rng.Name = "rngFeb"
>
> iStart = _
> Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> iEnd =
> Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> Set Rng = Range("A" & iStart & ":A" & iEnd)
> Rng.Name = "rngMar"
>

Thanks for your help.

Unfortunately it is not working for me ????

to avoid the error of having empty range i put on error resume next



Bob Phillips wrote:
> Dim iStart As Long
> Dim iEnd As Long
> Dim Rng As Range
> Dim i As Long
>
> With Sheets("Daily")
> For i = 1 To 12
> iStart = _
> .Evaluate("=MIN(IF(MONTH(DlyAll)=" & i & ",ROW(DlyAll)))")
> iEnd = _
> .Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> Set Rng = Range("A" & iStart & ":A" & iEnd)
> Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm")
> Next i
> End With
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Soniya" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have the following code to define a range based on month which is
> > sorted date wise.
> >
> > is there a way to avoid repeating the code 12 times for all months?
> >
> > also if there is no dates for a specific month say for november to
> > avoid causing an error while selecting the range since the range will
> > be empty?
> >
> > Thanks
> >
> >
> > Dim iStart As Long
> > Dim iEnd As Long
> > Dim Rng As Range
> >
> > iStart = _
> > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> > iEnd =
> > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > Rng.Name = "rngJan"
> >
> > iStart = _
> > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> > iEnd =
> > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > Rng.Name = "rngFeb"
> >
> > iStart = _
> > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> > iEnd =
> > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > Rng.Name = "rngMar"
> >

In what way?

--
HTH

Bob Phillips

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

"Soniya" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your help.
>
> Unfortunately it is not working for me ????
>
> to avoid the error of having empty range i put on error resume next
>
>
>
> Bob Phillips wrote:
> > Dim iStart As Long
> > Dim iEnd As Long
> > Dim Rng As Range
> > Dim i As Long
> >
> > With Sheets("Daily")
> > For i = 1 To 12
> > iStart = _
> > .Evaluate("=MIN(IF(MONTH(DlyAll)=" & i &
",ROW(DlyAll)))")
> > iEnd = _
> > .Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm")
> > Next i
> > End With
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Soniya" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > I have the following code to define a range based on month which is
> > > sorted date wise.
> > >
> > > is there a way to avoid repeating the code 12 times for all months?
> > >
> > > also if there is no dates for a specific month say for november to
> > > avoid causing an error while selecting the range since the range will
> > > be empty?
> > >
> > > Thanks
> > >
> > >
> > > Dim iStart As Long
> > > Dim iEnd As Long
> > > Dim Rng As Range
> > >
> > > iStart = _
> > > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> > > iEnd =
> > > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))")
> > > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > > Rng.Name = "rngJan"
> > >
> > > iStart = _
> > > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> > > iEnd =
> > > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2,ROW(DlyAll)))")
> > > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > > Rng.Name = "rngFeb"
> > >
> > > iStart = _
> > > Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> > > iEnd =
> > > Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3,ROW(DlyAll)))")
> > > Set Rng = Range("A" & iStart & ":A" & iEnd)
> > > Rng.Name = "rngMar"
> > >
>