none
Copy into empty cell RRS feed

  • Question

  • Hi guys

    In column A of my spreadsheet i have a number of blank cells. I would like a piece of code that when it finds an empty cell in column A it looks at the cell above and copies it into the blank cell. I always want to copy the info from the cell above. The number of rows within column A alters so I'd like it to apply this logic until the last row in the sheet.

    Thanks in advance.

    Wednesday, October 5, 2011 4:30 PM

Answers

  • This worked for me:

     

    Sub FillEmptyCell()
      ' set range to your convenience
        For Each c In Worksheets("Sheet1").Range("A1:A20").Cells
            ' check for emty cell
            If IsEmpty(c.Value) Then
               ' copy value from above cell to empty cell
               c.Value = c.Offset(-1, 0).Value
            End If
        Next
    End Sub

    Hope this helps,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Edited by danishaniModerator Thursday, October 6, 2011 1:27 AM layout
    • Marked as answer by ByronOne Thursday, October 6, 2011 8:31 PM
    Thursday, October 6, 2011 1:26 AM
    Moderator

All replies

  • This worked for me:

     

    Sub FillEmptyCell()
      ' set range to your convenience
        For Each c In Worksheets("Sheet1").Range("A1:A20").Cells
            ' check for emty cell
            If IsEmpty(c.Value) Then
               ' copy value from above cell to empty cell
               c.Value = c.Offset(-1, 0).Value
            End If
        Next
    End Sub

    Hope this helps,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Edited by danishaniModerator Thursday, October 6, 2011 1:27 AM layout
    • Marked as answer by ByronOne Thursday, October 6, 2011 8:31 PM
    Thursday, October 6, 2011 1:26 AM
    Moderator
  • Thanks so much for this Danishani!!

    Much appreciated...

    Thursday, October 6, 2011 8:31 PM
  • You are very welcome, glad to help! :)

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 8:33 PM
    Moderator