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

dpalomino1009

New Member
Joined
Mar 3, 2019
Messages
20
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.

Code:
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,252
Office Version
  1. 365
Platform
  1. Windows
Try this. I changed the input from D1 to E1 because the table wanted to insert that cell into a new table column.

Code:
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

New Member
Joined
Mar 3, 2019
Messages
20
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,252
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Mar 3, 2019
Messages
20

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,252
Office Version
  1. 365
Platform
  1. Windows
We're straying pretty far from the sample data in the OP now. Give this a shot.

Code:
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.
 
Last edited:

dpalomino1009

New Member
Joined
Mar 3, 2019
Messages
20

ADVERTISEMENT

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.

Code:
.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.

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

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

dpalomino1009

New Member
Joined
Mar 3, 2019
Messages
20
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!

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

dpalomino1009

New Member
Joined
Mar 3, 2019
Messages
20
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!

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

Forum statistics

Threads
1,141,301
Messages
5,705,583
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top