Set named ranges using VBA

Dear Experts:

I recently posted a similar questions, but this has a different requirement

I got thousand of sorted dates in Column A (in German Format

- Column A is sorted, starting from 01.01.2014 and ending at 31.12.2014.
- There are multiple occurrences of dates, i.e. 07.05.2014 can occur e.g. 20 times.

A macro should perform the following action:

- Set a range for all dates within the different months of the year:
- Name these ranges: MyRange_Jan14, MyRange_Feb14, MyRange_March14, MyRange_Apr14 till MyRange_Dec14.
- The column letter of the ranges has to be the column next to it, i.e. Column B

January dates range from A2:A288,  the named range, MyRange_Jan14 will range from: B2:B288
February dates range from A289: A508, the name range, MyRange_Feb 14 will range from B289:B508
and so forth

I have attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this code..This will do what you are looking for...

Sub putnamernge()

    Dim srow As Long
    Dim erow As Long
    Dim mnth As String
    Dim cell As Range, rng As Range
    Dim lrow As Long
    Dim r As Range
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A3:A" & lrow)

    srow = 2
    mnth = Format(Range("A2").Value, "mmm_yy")
    For Each cell In rng

        If Format(cell.Value, "mmm_yy") = mnth Then

            erow = cell.Row - 1
            Set r = ActiveSheet.Range("A" & srow & ":A" & erow)
            ActiveWorkbook.Names.Add Name:="Myrange_" & mnth, RefersTo:=r

            mnth = Format(cell.Value, "mmm_yy")
            srow = cell.Row
        End If

    Next cell
End Sub

Open in new window

Enclosed is your workbook..Post i have ran the macro...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Wow Saurabh, you did it again, exactly what I was looking for.  

Great job!!!!

Thank you very much for your professional help.
Regards, Andreas
Saurabh Singh TeotiaCommented:
Yw..Glad to Help.. :-)

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

I just detected, that the last month, ie. december is not assigned a named range by running the macro.

Strange, isn't it? Any idea why?

Regards, Andreas
Saurabh Singh TeotiaCommented:
Good spot..

In line-9 which is this...

 lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Open in new window

Change this to this....

 lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row+1

Open in new window

Andreas HermleTeam leaderAuthor Commented:
Great this did the trick. Thank you very much for your superb and professional help. Regards, Andreas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.