Fill Blank Cells between Two Specific Value Cells

[madhuchelliah]

Hi Folks, i need your help to solve my requirement. I want to fill the blank cells between two cells having specific value.
For example in A column i am having letters of N and Y repeatedly. Now i want to fill the blank cells between N and Y with Y letter. Blank cells between Y and N should remain blank. This N and Y combination could be more in the column. So the range should be up to last N and Y combination of the column.
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]N
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Y
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this for data in column "A":-

[COLOR="Navy"]Sub[/COLOR] MG02Dec04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
    [COLOR="Navy"]If[/COLOR] Not Rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
            [COLOR="Navy"]If[/COLOR] Dn(1).Offset(-1) = "N" [COLOR="Navy"]Then[/COLOR] Dn.Value = "Y"
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[madhuchelliah]

Try this for data in column "A":-

[COLOR=Navy]Sub[/COLOR] MG02Dec04
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
    [COLOR=Navy]If[/COLOR] Not Rng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Areas
            [COLOR=Navy]If[/COLOR] Dn(1).Offset(-1) = "N" [COLOR=Navy]Then[/COLOR] Dn.Value = "Y"
        [COLOR=Navy]Next[/COLOR] Dn
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thank you Mike. Its working perfectly

 

[MickG]

You're welcome