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?
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...

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.. :-)

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.