Delete cells in a TABLE that do not contain a specific string

Zale

New Member
Joined
Feb 5, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have a table, and in one of the columns I would like to run a macro that will delete all of the cells that do not contain a certain string, lets say "PRIME".
Then to shift the values in the column up so everything is nice and neat.
The length of this column of values will vary depending on the sheets input, so I want to use the table column as the range.
I have found similar questions, but the all seem to be for raw data in a sheet, not a table, and I cant get them to work for me.

Many thanks,
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
When you indicate that you want to delete the cell values, does that also mean that you want to delete the entire row or just the cell values? Can you provide a sample of 8-10 records that are representative of your actual file and then show us a mocked up solution. Please use XL2BB
 
Upvote 0
If I understood you correctly

VBA Code:
Sub jec()
 Dim j As Long
 Application.ScreenUpdating = False
 With Sheet2.Cells(1).CurrentRegion
    .Parent.ListObjects(1).Unlist
    For j = .Rows.Count To 1 Step -1
      If InStr(.Cells(j, 1), "PRIME") = 0 Then .Cells(j, 1).Delete
    Next
    .Parent.ListObjects.Add 1, .Offset, , 1
 End With
End Sub
 
Upvote 0
Hi, Thanks for the quick replies.
As an example, I currently have a macro that takes the column and trims off any spaces, and the pastes the values into another column, and then removes the duplicates.
This all works quite nicely using the column names as the range selections. Ideally I want to graft in some code into the middle of this code to add a step that will filter out all the strings that dont have "PRIME" in them.
'Trims leading spaces

VBA Code:
Dim Cel As Range

    Set rng = Range("LIST_TABLE[List number]")
   
    
    rng.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
    
    For Each Cel In rng
        Cel.Value = Application.Trim(Cel.Value)
    Next

Set rWorkRange = Nothing
Set rng = Nothing

'Moves list numbers to output table and removes duplicates
    Range("LIST_TABLE[List number]").Copy
    Range("LIST_OUTPUT[Filtered List Numbers]").PasteSpecial Paste:=xlPasteValues
    Range("LIST_OUTPUT[Filtered List Numbers]").RemoveDuplicates Columns:=1
End Sub
 
Upvote 0
Hi All,
After much trial and error, I managed to get the following to work for me.
VBA Code:
Sub Macro3()

Dim ws As Worksheet
    Dim tbl As ListObject
    Dim i As Long
    Dim searchString As String
    
    ' Set the worksheet and table
    Set ws = ThisWorkbook.Sheets("List Names")
    Set tbl = ws.ListObjects("LIST_OUTPUT")
    
    ' Define the search string
    searchString = "ENGINE AR-PRIM"
    
    ' Loop through the table rows from bottom to top
    For i = tbl.ListRows.Count To 1 Step -1
        ' Check if the search string is found in the first column of the table
        If InStr(1, tbl.ListRows(i).Range(1, 1).Value, searchString, vbTextCompare) = 0 Then
            ' Delete the row if the condition is met
            tbl.ListRows(i).Delete
        End If
    Next i
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

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
Back
Top