Clear cells based on a cell having the same value as cell in a table column range

[dpalomino1009]

Hello, I wanted to know if there was a way to clear cells based on a cell having the same value as cell in a table column range. For example, say we are working with table1, A1:C150, A has names, B has extensions, and C has computer names. If an extension in range B1:150 is the same as D1, B30 happens to match D1 for example, then the code would clear the corresponding cells. I would enter the extension I want cleared out on D1 and the code would clear the matching cells A30, C30 and B30. I attempted to put something together but need help. Noting some ideas I had below. I don't know if there is something that will select the cells that have the same value as D1 in the way that =INDEX(A1:A150,MATCH(D1,B1:B150,0)) would get a value in A1:A150 that matches D1 if found in B1:B150. I appreciate any ideas that you may have. Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D1")) Is Nothing Then        With Sheets("Ext, PC")
            "something to select the cell that has the same value as D1".Offset(0, -1).Clear
            "something to select the cell that has the same value as D1".Offset(0, 1).Clear
            "something to select the cell that has the same value as D1".Offset(0, 0).Clear
        End With


End If
End Sub

 

[lrobbo314]

Try this. I changed the input from D1 to E1 because the table wanted to insert that cell into a new table column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E1"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim tbl As ListObject: Set tbl = Me.ListObjects("Table1")
    tbl.Range.AutoFilter Field:=2, Criteria1:=Target.Value, Operator:=xlAnd
    tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    tbl.Range.AutoFilter
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If
End Sub

 

[dpalomino1009]

Thank you for looking into it. That would delete the entire row, but I have data on other parts of the row within the table. I simplified it just for the example. Is there a way to only delete those 3 cells as on the example from before A30, B30 and C30 depending on the matching extension on B30 and E1.

 

[lrobbo314]

Gotcha. Since we're dealing with more columns, I changed the input again to K1. I also added in some error handling for cases where a match is not found.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERH:
If Not Intersect(Range("K1"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim tbl As ListObject: Set tbl = Me.ListObjects("Table1")
    tbl.Range.AutoFilter Field:=2, Criteria1:=Target.Value, Operator:=xlAnd
    tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Resize(, 3).ClearContents
    tbl.Range.AutoFilter
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If


Exit Sub


ERH:


If Err.Number = 1004 Then
    'Value not found.
    If tbl.AutoFilter.Filters.Count > 0 Then tbl.Range.AutoFilter
    Application.EnableEvents = True
    Application.ScreenUpdating = True
Else
    MsgBox Error$, vbCritical, "Error"
End If
End Sub

 

[dpalomino1009]

Thank you, that works, but it also hides all of the rows except for the one that had the 3 cells to be deleted. I think I wasn't giving enough information before. I was hoping to change a desk number value in K1 for example, which changes a value in L1, then have the code look for the updated value in L1 on D1:150. If it happens to match D30 on table1, then clear the contents of B30, C30, G30, H30, and F30. I placed F30 at the end because that triggers another event that sorts the table. I'm leaving A30, D30, E30 because it has info that will apply to another user and will get sorted to the bottom of the list. I appreciate any ideas you may have on how to do this.

 

[lrobbo314]

We're straying pretty far from the sample data in the OP now. Give this a shot.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERH:
If Not Intersect(Range("K1"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim r As Range
    Dim tbl As ListObject: Set tbl = Me.ListObjects("Table1")
    tbl.Range.AutoFilter Field:=4, Criteria1:=Target.Offset(, 1).Value, Operator:=xlAnd
    Set r = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each ar In r.Areas
        Application.Union(ar.Columns("B:C"), ar.Columns("F:H")).ClearContents
    Next ar
    tbl.Range.AutoFilter
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If


Exit Sub


ERH:


If Err.Number = 1004 Then
    'Value not found.
    If tbl.AutoFilter.Filters.Count > 0 Then tbl.Range.AutoFilter
Else
    MsgBox Error$, vbCritical, "Error"
End If


Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I commented the code for the place to put your code that is triggered by column F changing.

 

[dpalomino1009]

Thank you, that works. Just a couple things I need some help on. The rows on the table hide except for the one that had the cells that get cleared, I don't know why or how to keep it from hiding after the code runs. Also, the table doesn't auto sort like it does when I change or clear a value in column F. Is there a way to keep it form auto hiding and to clear the value in F in a way that my other sorting macro will recognize with your coding?


I have used this part of code noted below and it does auto sort, it also gets triggered by a changing cell like the one I use on the this sheet.

.Range("F150").Value = Sheets("Welcome Email & New Hire Info").Range("H2").Value

Noting the part of the code that triggers the sorting in the table1 below.

If Not Intersect(Target, Range("F1:F150")) Is Nothing Then

Thank you for all of your help, I appreciate any suggestions.

 

[dpalomino1009]

Found this code to keep it from hiding or rather to unhide after it hides, but still not sure about the sorting part. I guess I could add the sorting macro again, that should work, but if you know of something less repetitive I don't know about, that'd be great. Again, thank you for your help!

Rows("1:150").EntireRow.Hidden = False

 

[dpalomino1009]

I think I just answered my own question. Added the code below at the end and it worked. Just set a range in F equal to a range in F (a blank cell) and it triggered the other sorting macro. I wouldn't have been able to get this working the way I wanted without you, but if you have something shorter or that makes more sense, suggestions are more than welcome, Thank you!

With Sheets("Ext, PC")    .Range("F150").Value = .Range("F152").Value
    End With