I need to compare 2 columns A and B where B has formula.

1. If ColumnA has extra data at the end and corresponding cells in column B is empty, then copy the Formula from above Row

2. If ColumnA is Empty and Column B has Formulas, then delete those Formulas!

Can we do this using VBA!?

CodePudding user response：

Your question actually leaves more unspecified than specified. The followed does what you want in the broadest possible interpretation.

Sub Correspond_Columns()

    Const stMT$ = "Correspond Columns"
    Dim lgR&, lgLastFormulaRow
    
''' Pocess all rows from 1 down to the last used row
    For lgR = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    
    ''' Get row number of lowest row with a formula in column B
        If Cells(lgR, 2).HasFormula Then lgLastFormulaRow = lgR
        
    ''' If cell in column A is not empty and there's no formula in corresponding column B:
    ''' o  If found row with formulas: Copy it to to here
    ''' o  Otherwise: Report and end
        If Cells(lgR, 1) <> "" Then
            If Not Cells(lgR, 2).HasFormula Then
                If lgLastFormulaRow > 0 Then
                    Cells(lgLastFormulaRow, 2).Copy
                    Cells(lgR, 2).PasteSpecial xlPasteFormulas
                Else
                    MsgBox Title:=stMT, Prompt:="Missing formula, but no preceding formula found"
                    Exit Sub
                End If
            End If
    
    ''' If cell in column A is empty: Clear whatever is in corresponding column B
        Else: Cells(lgR, 2).ClearContents
        End If
    Next lgR

End Sub

CodePudding user response：

Just to check: this would be very easy to do by hand, using control to navigate to the bottom of a column, then double clicking the last formula in B to extend it along A, or shift-ctrl-down to select the remaining formulas in B and delete. But assuming you really do need to use VBA (perhaps you have many such files):

In VBA, it would be something like this: iterate down the column, check for both cells empty to end the loop. if A is empty, delete B; if B is empty, copy above function.

Assuming column A is column 1, B is 2:

Sub CheckEmptyColumns()
 i = 2
 Do Until IsEmpty(Cells(i, 1) AND IsEmpty(Cells(i,2)))
   If IsEmpty(Cells(i,1)) Then
      'A is empty, B is not: Clear B
      Cells(i,2).Clear
   ElseIf IsEmpty(Cells(i,2)) Then
      'B is empty, A is not: copy formula
      Cells(i,2).FormulaR1C1 = Cells(i-1,2).FormulaR1C1
   i = i   1
   Loop
End Sub

Edit: if your formula uses relative cells, use Cells.FormulaR1C1 insead of Cells.Formula -- Thanks Spinner for the nudge.