Results 1 to 9 of 9

Fill in blank cells in a range

This is a discussion on Fill in blank cells in a range within the Excel Questions forums, part of the Question Forums category; Hi there. Need some help please. I would like to get some code that will fill blanks in a range. ...

  1. #1
    Board Regular
    Join Date
    Jan 2012
    Posts
    101

    Default Fill in blank cells in a range

    Hi there. Need some help please.

    I would like to get some code that will fill blanks in a range.

    Forgive me if I have not used the board facilities properly. In the following table I need each blank cell in column B to be filled with the text from the preceding cell that has text in it.

    i.e. B2 to be populated with "text1". Cells B4:B6 to be populated with "text2".

    The code needs to know that row 9 is the last row to populate.

    Thanks in advance

    A B
    1 head text1
    2 acc
    3 head text2
    4 acc
    5 fry
    6 oth
    7 head text3
    8 acc
    9 oth
    10

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Fill in blank cells in a range

    You don't really need code for that...

    Highlight Column B
    Press CTRL + G
    Click Special
    Select Blanks
    Click OK
    Press =
    Press UP Arrow
    Press CTRL + ENTER


    Hope that helps.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    101

    Default Re: Fill in blank cells in a range

    Quote Originally Posted by Jonmo1 View Post
    You don't really need code for that...

    Highlight Column B
    Press CTRL + G
    Click Special
    Select Blanks
    Click OK
    Press =
    Press UP Arrow
    Press CTRL + ENTER


    Hope that helps.
    Brilliant! Never knew about that. Thank you very much.

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Fill in blank cells in a range

    Glad to help, thanks for the feedback.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    101

    Default Re: Fill in blank cells in a range

    Quote Originally Posted by Jonmo1 View Post
    Glad to help, thanks for the feedback.
    Maybe you can help with this bit of my code too.

    I have declared lrow as long and identified row 9 as the last row to be populated.

    I have written:

    Range("E3:E " & lrow & " ").Select

    The syntax is wrong and I don't know why. Can you help?

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Fill in blank cells in a range

    Range("E3:E" & lrow).Select


    Though it's rarely necessary to select ranges to manipulate them..
    You can refer to them directly
    Range("E3:E" & lrow).Value = "Hello"
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Posts
    101

    Default Re: Fill in blank cells in a range

    Quote Originally Posted by Jonmo1 View Post
    Range("E3:E" & lrow).Select


    Though it's rarely necessary to select ranges to manipulate them..
    You can refer to them directly
    Range("E3:E" & lrow).Value = "Hello"
    So how should this look?

    Range("E3:E" & lrow).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Fill in blank cells in a range

    General rule of thumb for removing Select and Selection...

    Work from the bottom UP..
    If you see Selection, just replace that with whatever was previously selected.

    so this
    Range("E3:E" & lrow).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection
    .FormulaR1C1 = "=R[-1]C"

    becomes

    Range("E3:E" & lrow).Select
    Selection
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

    becomes

    Range("E3:E" & lrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"


    Hope that helps.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    Board Regular
    Join Date
    Jan 2012
    Posts
    101

    Default Re: Fill in blank cells in a range

    Quote Originally Posted by Jonmo1 View Post
    General rule of thumb for removing Select and Selection...

    Work from the bottom UP..
    If you see Selection, just replace that with whatever was previously selected.

    so this
    Range("E3:E" & lrow).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection
    .FormulaR1C1 = "=R[-1]C"

    becomes

    Range("E3:E" & lrow).Select
    Selection
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

    becomes

    Range("E3:E" & lrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"


    Hope that helps.
    Genius!

    I don't think I will ever get to that level.

    Thanks again.

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
  •  


DMCA.com