Solved Excel Macro To Insert Row based on Comparison

May 20, 2015 at 11:05:49
Specs: Windows 8
So, I have a Excel spreadsheet of file paths (All in column A) and their respective end file (in column B) and I need the macro to go down column A and compare the file paths to one another, and when they are different it needs to insert a row underneath both column A and B where the path differs. Hopefully someone can help with this. Thanks in advance!

See More: Excel Macro To Insert Row based on Comparison

Report •


✔ Best Answer
May 20, 2015 at 14:31:48
Sub InsertRows() 
'Determine last Row with Data in Column A 
   lastRw = Cells(Rows.Count, "A").End(xlUp).Row 
'Loop through rows in reverse order, looking for a change in Column A
     For nxtRw = lastRw To 3 Step -1 
       If Cells(nxtRw - 1, "A") <> Cells(nxtRw, "A") Then 
'If a change occurs, Insert Row 
         Cells(nxtRw, "A").Insert 
       End If 
     Next 
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 20, 2015 at 11:40:54
re: insert a row underneath both column A and B

This requirement is unclear. In reality you have 2 choices:

1 - Insert an entire row at the change in Column A.
2 - Insert a cell in both Column A & B at the change in Column A, leaving Column C and beyond alone.

In other words, you can't insert a "row" in only 2 columns but you can insert cells. Which of the 2 options are you looking for?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 20, 2015 at 12:17:53
I guess it would be best to insert cells in both columns A & B.

Report •

#3
May 20, 2015 at 13:00:50
Is there data In Column C and beyond? If so, inserting cells in Columns A & B only will cause the data in Columns C and beyond to become misaligned with the data in Columns A & B.

You would start with this:

      A        B       C        D
1   Path1    File    Data1    Data11
2   Path1    File    Data1    Data11
3   Path2    File    Data2    Data22
4   Path2    File    Data2    Data22
5   Path3    File    Data3    Data33
6   Path3    File    Data3    Data33

And end up with this:

      A        B       C        D
1   Path1    File    Data1    Data11
2   Path1    File    Data1    Data11
3                    Data2    Data22
4   Path2    File    Data2    Data22
5   Path2    File    Data3    Data33
6                    Data3    Data33
7   Path3    File   
8   Path3    File   

Is that what you want? If there is no data in Columns C and beyond, then inserting an entire row is probably the best option since it won't affect anything.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 20, 2015 at 13:56:27
Yes that is fine, there would be no data in C or D

Report •

#5
May 20, 2015 at 14:31:48
✔ Best Answer
Sub InsertRows() 
'Determine last Row with Data in Column A 
   lastRw = Cells(Rows.Count, "A").End(xlUp).Row 
'Loop through rows in reverse order, looking for a change in Column A
     For nxtRw = lastRw To 3 Step -1 
       If Cells(nxtRw - 1, "A") <> Cells(nxtRw, "A") Then 
'If a change occurs, Insert Row 
         Cells(nxtRw, "A").Insert 
       End If 
     Next 
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 21, 2015 at 08:56:36
Thank you very much!

Report •


Ask Question