Code Required Please

[Dazzawm]

I need a code that will firstly look at column A and column B. If the data in those are the same then look at the very first start year in column C and the very last year in column D and insert a row for the missing years. I hope the table explains better.



<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 99px"><COL style="WIDTH: 129px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left">Make</TD><TD style="TEXT-ALIGN: left">Model</TD><TD style="TEXT-ALIGN: left">SY</TD><TD style="TEXT-ALIGN: left">EY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD><TD style="TEXT-ALIGN: left">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1973</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1975</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1973</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1983</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD><TD style="TEXT-ALIGN: left">1983</TD></TR></TBODY></TABLE>

As you can see in the example all the data in each row in column A & B are the same. It needs to look at the lowest year in all the rows in column C which is 1963 and the latest year in column D which is 1983 and then make sure there is an entry for every year in between these 2 years. Then when the data changes in column A & B do the same again. Then if possible delete column D at the very end of the code as it is not needed So this is what it will look after. I have coloured what the code needs to add.

The cells not coloured were the ones that were already in column C!

I hope this makes sense! Thanks.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 99px"><COL style="WIDTH: 129px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left">Make</TD><TD style="TEXT-ALIGN: left">Model</TD><TD style="TEXT-ALIGN: left">SY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1963</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1964</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1965</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1966</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1967</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1968</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1969</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1971</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1972</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1973</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1974</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1975</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1976</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1977</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1978</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left">1979</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1980</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1981</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1982</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left">Ford</TD><TD style="TEXT-ALIGN: left">Cortina</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00">1983</TD></TR></TBODY></TABLE>

 

[Dazzawm]

Thanks Mick can you explain how the input box works and is it possible to copy that data without the box? Thanks.

 

[MickG]

I was not sure what you actually wanted , so it was a bit of a guess!!
The ideas is the resultant data (as per the previous code for the 5 columns ) is pasted started "H2", then you insert a column value for the combined values of columns 6 and 7 to be pasted to.
Can you describe in more detail what you want.
Mick

 

[Dazzawm]

It is more or less what I want but I dont want the data in F & G added together I need them in seperate columns. So looking at my latest table and Ford Cortina 1.2. Once the code has run and we have

Ford Cortina 1.2 1963
Ford Cortina 1.2 1964
Ford Cortina 1.2 1965

Then I need what is in column F and G put in L & M so it looks like

Ford Cortina 1.2 1963 Petrol FDCOR_12A
Ford Cortina 1.2 1964 Petrol FDCOR_12A
Ford Cortina 1.2 1965 Petrol FDCOR_12A

Thanks

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Oct50
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Twn     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tot     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Twn = Dn & Dn(, 2) & Dn(, 3)
                [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                    .Add Twn, Array(Dn, Dn(, 2), Dn(, 3), Dn(, 4), Dn(, 5), Dn(, 6), Dn(, 7))
                [COLOR="Navy"]Else[/COLOR]
                    Q = .Item(Twn)
                        Q(3) = Application.Min(Q(3), Dn(, 4))
                        Q(4) = Application.max(Q(4), Dn(, 5))
                    .Item(Twn) = Q
                [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    tot = tot + (.Item(K)(4) - .Item(K)(3))
[COLOR="Navy"]Next[/COLOR] K
ReDim ray(1 To tot, 1 To 6)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
      [COLOR="Navy"]For[/COLOR] ac = .Item(K)(3) To .Item(K)(4) - 1
            c = c + 1
            ray(c, 1) = .Item(K)(0)
            ray(c, 2) = .Item(K)(1)
            ray(c, 3) = .Item(K)(2)
            ray(c, 4) = ac
            ray(c, 5) = .Item(K)(5)
            ray(c, 6) = .Item(K)(6)
        [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]Next[/COLOR] K
Range("H2").Resize(tot, 6) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Thanks Mick that seems to be it. Thanks for all your time and effort. Watch this space as I may need other columns added too! lol.

 

[Dazzawm]

Hi Mick. A small problem. When using this code on a certain file it only goes down a certain amount of data and stops. i.e the make and models etc go from A-Z and the code stops when it is a small way through V?!

 

[Dazzawm]

Further to my previous post, I have tried cutting the file in 2 - A-O and P-Z in case the file was too big but both finish before the end. A-O stops somewhere in M and P-Z stops somewhere in V?

 

[MickG]

Can you post the data or similar !!!
Mick

 

[Dazzawm]

Would it be better if I sent you the file?

 

[Dazzawm]

I must apologise Mick but I tweaked your code a little which may explain why it doesnt work correctly. In a previous post I said I did not need the last year as a row but I do now. I ran your code and it does the whole file. Shall I post what the code is now after I tweaked it?