Insert Cell On Condition And Move Right

  • Hi all,

    I have made really huge downloads out of our SAP system (needed over 20 separate Excel files ;-). I need to analayze the data, but unfortunately the date is not 100% consistent as some cells are emtpy.

    Is it possible to have Excel look down a column, and have it insert a blank cell in another column on the same row where it found the empty cell? (and move the cells right a a result?)

    I have attached an example of what Excel should do as it is hard to explain (as you can tell ;-). Please can you help me on this? I am likely to spend the next days figuring out how I can best combine the data and analyze it!:rolleyes:

    Thanks in advance

  • Re: Insert Cell On Condition And Move Right

    Hi Koen,

    The following code should do it.

    Sub Replace()
        Dim lRow As Long
        lRow = Range("A65536").End(xlUp).Row
        For i = 2 To lRow
            If Range("I" & i).Value = "" Then
                Range(Range("G" & i), Range("I" & i)).Value = Range(Range("F" & i), Range("H" & i)).Value
                Range("F" & i).Value = 0
            End If
        Next i
    End Sub


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!