Announcement

Collapse
No announcement yet.

VBA To Fill Blank Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Vba To Fill Blank Cells

    Hi,

    Try:
    Code:
    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
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Vba To Fill Blank Cells

      This approach uses SpecialCells rather than Find.
      Code:
      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

      Comment


      • #4
        Re: Vba To Fill Blank Cells

        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
        Code:
        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, 09:57.

        Comment


        • #5
          Re: Vba To Fill Blank Cells

          Nice work jindon.
          Reafidy

          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

          Comment


          • #6
            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

            Comment


            • #7
              Re: VBA To Fill Blank Cells

              Excellent results. Thanks everbody.

              Comment

              Working...
              X