Results 1 to 7 of 7

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

  1. #1
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,235
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    There isn't a day go by without me learning something new about VBA!

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

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

    Try:-
    Code:
    Sub MG14Nov30
    Dim Rng As Range, Dn As Range, Temp As Variant
    Set Rng = Range("M7", Range("M" & Rows.Count).End(xlUp))
    For Each Dn In Rng
        If Dn <> "" Then Temp = Dn.Value
        Dn.Value = Temp
    Next Dn
    End Sub
    Regards Mick

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    See if this does what you want:
    Code:
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

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

    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.
    Last edited by MickG; Nov 14th, 2018 at 09:38 AM.

  5. #5
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,235
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Guys - thanks to both, your suggestions work a treat and give me a number of options.
    There isn't a day go by without me learning something new about VBA!

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    Glad we were able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by sharky12345 View Post
    Guys - thanks to both, your suggestions work a treat and give me a number of options.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

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

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •