SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Copy previous cell data in Excel – Excel Macro

on May 3, 2012

Let’s say you have a list of entries in column A, similar to the above example, and within the list you have many blank cells.
Here is a quick and easy way to fill those blanks with the value of the cell above.
Using VBA Macro:

Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
    If Selection.Cells.Count = 1 Then
        MsgBox "You must select your list and include the blank cells", vbInformation
            Exit Sub

    ElseIf Selection.Columns.Count > 1 Then
        MsgBox "You must select only one column", vbInformation
            Exit Sub
    End If

    Set rRange1 = Range(Selection.Cells(1, 1), Cells(65536, Selection.Column).End(xlUp))

    On Error Resume Next
    Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If rRange2 Is Nothing Then
        MsgBox "No blank cells Found", vbInformation
        Exit Sub
    End If

    rRange2.FormulaR1C1 = "=R[-1]C"

    iReply = MsgBox("Cells copied", vbYesNo + vbQuestion)
    If iReply = vbYes Then rRange1 = rRange1.Value

End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: