EXCEL VBA help - function to insert blank cell

Question

Hi,

I need some help with an excel VBA function. I have data that looks like this

    ColA     ColB
    a123     a123
    a124     a124
    a127     a126
    a128     a127
    ....     ....

I want to compare the contents of ColA and ColB, Where the contents are different I want to insert a blank cell into column A. So the result will look like:

ColA     ColB
a123     a123
a124     a124
         a126
a127     a127
....     ....

Any suggestions as to how I could do this in Excel.

Thanks in advance

---

UPDATED

---

I tried the method below with the following code to insert the cell and it is working fine, I now realise when I run it that I need some more functionality though.

first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown

if the value in ColA with the "a" removed is less than the value in ColB with the "a" removed I want to insert the cell in in ColA and I also need to insert a cell in the same position in ColC (contains other data). If ColB has a larger value I want to insert the cell in ColB only. I think I know what to do inside the If statement but I'm not sure how to construct the IF. Here is what I am thinking

Set other_col = Range("C1:C100")

//if substring of ColA > substring of ColB
   first_col.Cells(row, 1).Select
   Selection.Insert Shift:=xlDown
   other_col.Cells(row, 1).Select
   Selection.Insert Shift:=xlDown
//else 
   second_col.Cells(row, 1).Select
   Selection.Insert Shift:=xlDown

Answer 1

I don't know the exact VBA code to insert a row, but you can find that by recording a macro. Here is the rest of the code (to loop through the columns and do the comparison):

Sub Expand()
    Dim first_col as Range
    Dim second_col as Range
    Dim row as Integer

    Set first_col = Range("A2:A100")
    Set second_col = Range("B2:B100")

    For row = 1 To second_col.Rows.Count
        If first_col.Cells(Row, 1).Value <> second_col.Cells(Row, 1).Value Then
            '// code to insert the row'
        End If
    Next row
End Sub

Answer 2

Your comparison statement could look something like this:

Sub Expand()

    Dim first_col As Range, cell As Range
    Dim row As Integer

    Set first_col = Range("A2:A100")

    For Each cell In first_col
        If Right(cell.Value, 3) < Right(cell.Offset(0, 1).Value, 3) Then
            ' Shift in here
        End If
    Next cell

End Sub

The "Right" function looks at the number of characters from the right as specified by the second parameter.

One other note is that code sections like this that use the .select method:

first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown

Can be truncated like this:

first_col.cells(row, 1).insert shift:=xlDown

The "select" and "selection" are leftovers from the macro recorder and usually aren't used in VBA code.