Fill down column until next non-blank row then start again

[sharky12345]

I have some cells with data in column M between M7:M100 - only cells M7, M16, M26, M35 and M42 have values. I need a VBA routine that will start at M7 and fill M6:M15 with the same value, then start again from M16 and so on.

Can anyone start me off please?

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG14Nov30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("M7", Range("M" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn <> "" [COLOR="Navy"]Then[/COLOR] Temp = Dn.Value
    Dn.Value = Temp
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[My Aswer Is This]

See if this does what you want:

Sub Fill_Empty_Cells()
'Modified  11/14/2018  9:22:55 AM  EST
Dim r As Range
    For Each r In Range("M7:M100")
        If r.Value = "" Then r.Value = r.Offset(-1).Value
    Next
End Sub

 

[MickG]

Another option:-
Fill Blank Cell Down Column
1. Select all of the data in column "M", from top to bottom.
2. Click on Home->Find & Select->Go To > Special
3. Click "Blanks" and hit "Ok". (At this point, all blanks in the column should be selected, and cell A2 should be the active cell.)
4. Press "=", and then press up to select cell M7.
5. Press CtrlEnter to copy the formula into all selected cells.

 

[sharky12345]

Guys - thanks to both, your suggestions work a treat and give me a number of options.

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Guys - thanks to both, your suggestions work a treat and give me a number of options.

 

[MickG]

You're welcome