Comparing two arrays in VBA and adding strings

Question

since I'm pretty new to VBA I need help with a part of my code that should compare two arrays, sorted in ascending order, and if there is no value in any of the arrays, then it should add a row to the corresponding table and fill the missing value with a zero value in the cell next to her. This is what I have so far:

With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For I = 3 To LastRow If Cells(I, 1) > Cells(I, 6) Then LastRow = LastRow + 1 With Range("A" & I & ":C" & I) .Select .Insert Shift:=xlDown End With Range("A" & I) = Cells(I, 6) Cells(I, 2).Value = 0 ElseIf Cells(I, 1).Value < Cells(I, 6).Value Then With Range("F" & I & ":H" & I) .Select .Insert Shift:=xlDown End With Range("F" & I) = Cells(I, 1) Cells(I, 7).Value = 0 Else End If Next i 

The problem with this code, in addition to its inefficiency (which is not a problem, since both arrays are very small) is that LastRow: a) changes with each row added, b) only takes into account LastRow in array1, therefore, if array2 is larger, it doesn't go all the way down, c) if the cell is empty, it adds a row with an empty cell to the corresponding array, even if I add

 If Not IsEmpty (Cells(i, 1)) And IsEmpty(Cells(i, 6)) Then 'next i 

I know that the solution is probably related to defining both arrays and using LBound for Ubound, but I could not circle it around. Many thanks for the help!

EDIT: The last line seems to be fixed now, however I still can’t somehow skip the empty cells and the cell in the last one that contains the text “Grand Total” inside and therefore is not sorted unlike the rest of the range. Anyone have any ideas how to get around this? This is what the code looks like:

 CurrentRow = 3 Do While CurrentRow <= LastRow If Cells(CurrentRow, 1) > Cells(CurrentRow, 6) Then If Not Cells(CurrentRow, 6).Value = "Grand Total" Or IsEmpty(Cells(CurrentRow, 6).Value) Then With Range("A" & CurrentRow & ":C" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("A" & CurrentRow) = Cells(CurrentRow, 6) Cells(CurrentRow, 2).Value = 0 End If ElseIf Cells(CurrentRow, 6) > Cells(CurrentRow, 1) Then If Not Cells(CurrentRow, 1).Value = "Grand Total" Or IsEmpty(Cells(CurrentRow, 1).Value) Then With Range("F" & CurrentRow & ":H" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("F" & CurrentRow) = Cells(CurrentRow, 1) Cells(CurrentRow, 7).Value = 0 End If Else End If With ActiveSheet LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row End With CurrentRow = CurrentRow + 1 Debug.Print CurrentRow Loop 

EDIT 2: I figured it out finally! I added another condition to add a row to the opposite table if it finds the value "Grand Total". No need to worry about empty cells!

 CurrentRow = 3 Do While CurrentRow <= LastRow If Cells(CurrentRow, 1) > Cells(CurrentRow, 6) Then If Cells(CurrentRow, 6).Value = "Grand Total" Then With Range("F" & CurrentRow & ":H" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("F" & CurrentRow) = Cells(CurrentRow, 1) Cells(CurrentRow, 7).Value = 0 Else With Range("A" & CurrentRow & ":C" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("A" & CurrentRow) = Cells(CurrentRow, 6) Cells(CurrentRow, 2).Value = 0 End If ElseIf Cells(CurrentRow, 6) > Cells(CurrentRow, 1) Then If Cells(CurrentRow, 1).Value = "Grand Total" Then With Range("A" & CurrentRow & ":C" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("A" & CurrentRow) = Cells(CurrentRow, 6) Cells(CurrentRow, 2).Value = 0 Else With Range("F" & CurrentRow & ":H" & CurrentRow) .Select .Insert Shift:=xlDown End With Range("F" & CurrentRow) = Cells(CurrentRow, 1) Cells(CurrentRow, 7).Value = 0 End If Else End If With ActiveSheet LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row End With CurrentRow = CurrentRow + 1 Debug.Print CurrentRow Loop 

Answer 1

good neat question for change! Here are my thoughts on the first two parts:

a) with a for loop, the initial conditions (Lastrow in this case) are read only when the loop starts, so if you change Lastrow during the loop, the loop will only run to the original value.

to get around this, you could restructure it like a while loop. using a general example:

 Sub loop_for() Dim intLoop As Integer Dim intEnd As Integer intEnd = 3 For intLoop = 1 To intEnd 'this is fixed as soon as is triggered Debug.Print intLoop If intLoop = 2 Then intEnd = 4 'this has no effect on loop Next intLoop 'output is 1,2,3 End Sub 

VS

 Sub loop_while() Dim intLoop As Integer Dim intEnd As Integer intLoop = 1 intEnd = 3 Do While intLoop <= intEnd Debug.Print intLoop intLoop = intLoop + 1 If intLoop = 2 Then intEnd = 4 Loop 'output is 1,2,3,4 End Sub 

b) why not just evaluate both and choose the larger of the two?

 Sub lastrow() Dim lastrow As Long Dim lastrow1 As Long Dim lastrow2 As Long lastrow1 = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row lastrow2 = ActiveSheet.Cells(.Rows.Count, "F").End(xlUp).Row lastrow = Application.Max(lastrow1, lastrow2) End Sub 

c) ended here, hope someone else can help.