Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA To Fill Blank Cells

  1. #1
    Join Date
    17th November 2006
    Location
    New Zealand
    Posts
    39

    VBA To Fill Blank Cells

    I have two columns with data (length of column B represents total length of database.) Column A only has some blank cells.

    I need to fill Column A blanks with the contents of cell above.

    Monday
    Tuesday
    [blank cell]
    Wednesday
    Thursday
    [blank cell]
    [blank cell]
    ***end of column B

    Therefore VBA to place Tuesday into blank and Thursday into the two blanks before bottom of the range of Column B.

    Using record macro doesnt give me go to first blank etc.

    What is the best way. - ?

    something like

    Start column A2 to End
    Find first blank, copy active cell -1
    Next blank cell. (ins some cases it will copy the name it has just filled into cell above.
    Finish at last cell in Comumn B

    Thanks
    Charlie

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,913

    Re: Vba To Fill Blank Cells

    Hi,

    Try:
    VB:
    Sub HTH() 
        Dim RowNo As Long, LastRow As Long 
        With Worksheets(1) 
            LastRow = .Range("B" & Rows.Count).End(xlUp).Row 
            Do 
                Set c = .Columns(1).Find("", after:=.[A2], LookIn:=xlValues) 
                If c Is Nothing Or c.Row > LastRow Then Exit Do 
                c.Value = c.Offset(-1).Value 
            Loop 
        End With 
    End Sub 
    
    

  3. #3
    Join Date
    23rd April 2007
    Posts
    3,417

    Re: Vba To Fill Blank Cells

    This approach uses SpecialCells rather than Find.
    VB:
    Sub test() 
        Dim xray As Range 
        With Sheets("sheet1") 
            On Error Resume Next 
            For Each xray In Range(.Range("b1"), .Cells(.Rows.Count, 2).End(xlUp)).Offset(0, -1).SpecialCells(xlCellTypeBlanks) 
                xray.Value = xray.Offset(-1, 0).Value 
            Next xray 
            On Error Goto 0 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,276

    Re: Vba To Fill Blank Cells

    Quote Originally Posted by sswcharlie
    I have two columns with data (length of column B represents total length of database.) Column A only has some blank cells.

    I need to fill Column A blanks with the contents of cell above.

    Monday
    Tuesday
    [blank cell]
    Wednesday
    Thursday
    [blank cell]
    [blank cell]
    ***end of column B
    No Loop
    VB:
    Sub test() 
        On Error Resume Next 
        With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(,-1).SpecialCells(4) 
            .FormulaR1C1 = "=R[-1]C" 
            .Value = .Value 
        End With 
    End Sub 
    
    
    Edited: code
    Last edited by jindon; July 10th, 2007 at 09:57.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,913

    Re: Vba To Fill Blank Cells

    Nice work jindon.

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Vba To Fill Blank Cells

    We have an free add-in zip for this here called Fill Blanks Excel Add-in Fill blanks in a list with the cell above

    Code used is shown here with details on code and an esay formula manual method: Excel Formula and VBA Macro Code To Fill All Blank Cells In a List

  7. #7
    Join Date
    17th November 2006
    Location
    New Zealand
    Posts
    39

    Re: VBA To Fill Blank Cells

    Excellent results. Thanks everbody.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: April 18th, 2008, 06:09
  2. Fill Blank Cells Going Up
    By kherbert in forum EXCEL HELP
    Replies: 5
    Last Post: April 3rd, 2008, 14:25
  3. Fill Blank Cells In Range
    By PeterMao in forum EXCEL HELP
    Replies: 4
    Last Post: December 9th, 2006, 03:50
  4. Fill Down in Blank cells only
    By AYCH in forum EXCEL HELP
    Replies: 7
    Last Post: November 1st, 2005, 15:28
  5. Fill Blank Cells
    By alexanderd in forum EXCEL HELP
    Replies: 2
    Last Post: April 22nd, 2004, 05:43

Bookmarks

Posting Permissions

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