VBA / Function to Delete Empty Rows of Linked Workbooks [Image Provided]

[CuriousForge]

These 2 Workbooks are linked.

I want to delete the blank rows of Table 2 (Right side) so that the filled rows get sequentially arranged.
Need a function/vba to run through Table 1 (left) each time from start to reassess this and delete the blanks of Table 2 because some 'Approved' may be changed to 'Declined' later or vice versa

Help, anyone?.

 

[igold]

I have some questions:

In what way are the workbooks linked. Can the cells on the right be overwritten or will that mess with the linking.

Are they really tables or ranges of data?

 

[CuriousForge]

I have some questions:

In what way are the workbooks linked. Can the cells on the right be overwritten or will that mess with the linking.

Are they really tables or ranges of data?

Each cell on the Right Table is linked with the following formula on the Decision column

=IF('[1.xlsb]Sheet1'!$C2="Declined",'[1.xlsb]Sheet1'!$C2,"")

and if that decision is met with 'Declined' then the Date and Name column get linked with these formulas

=IF(C2="Declined",'[1.xlsb]Sheet1'!$B2,"")

=IF(C2="Declined",'[1.xlsb]Sheet1'!$A2,"")

 

[CuriousForge]

It's a row wise cell to cell link.

 

[igold]

I am not sure exactly what you want. I guess the phrase "sequentially arranged" is throwing me.

That said, if you wanted something that will give you the results that you show in your OP, this code would do that. The left side of your graphic would be in Workbook name "1.xlsb" and right side would be in Workbook "2.xlsb". The code would rewrite "Sheet1" in Workbook 2.xlsb with what you have shown.

I do not know what else is in Workbook 2 so if you wanted to automate this you could either put the code or a call to the code in either the Worksheet_Activate() or Workbook_Open() events. Both workbooks should be open.

I hope this makes sense. Please test on a backup copy of your data.

Sub DecisionUpdate()


    Dim Decision As Variant
    Dim lRow As Long, i  As Long
    
    lRow = Workbooks("1.xlsb").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Decision = Workbooks("1.xlsb").Sheets("Sheet1").Range("A2:C" & lRow)
    For i = LBound(Decision) To UBound(Decision)
        If Decision(i, 3) = "Approved" Then
            Decision(i, 1) = ""
            Decision(i, 2) = ""
            Decision(i, 3) = ""
        End If
    Next
    Workbooks("2.xlsb").Sheets("Sheet1").Range("A2").Resize(UBound(Decision, 1), UBound(Decision, 2)) = Decision
    
    
End Sub

 

[CuriousForge]

Thanks for the code. Unfortunately, it isn't doing what I'm hoping for.

By "sequentially arranged" I simply mean the blank/unmatched rows (that are results of 'Approved') in Table 2 should be eliminated and only the visible results of 'Declined' should appear in a sequence as 1,2,3,4 etc.

 

[igold]

Does this do what you want...

Sub DecisionUpdate()


    Dim Decision As Variant
    Dim lRow As Long, i  As Long
    
    lRow = Workbooks("1.xlsb").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Decision = Workbooks("1.xlsb").Sheets("Sheet1").Range("A2:C" & lRow)
    For i = LBound(Decision) To UBound(Decision)
        If Decision(i, 3) = "Approved" Then
            Decision(i, 1) = ""
            Decision(i, 2) = ""
            Decision(i, 3) = ""
        End If
    Next
    With Workbooks("2.xlsb").Sheets("Sheet1")
        .Range("A2").Resize(UBound(Decision, 1), UBound(Decision, 2)) = Decision
        .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    
End Sub