Hi . I am looking to find all cells that have 5 digits in (they are addresses so will have other text also in cell) and then delete all rows at once

[nickkmal]

For example, I want to find (in this example) 90017 and then delete this whole row, and I have hundreds of similar cells on the spreadsheet where I would all cells highlighted that have 5 digits and the whole row of those highlighted cells deleted

3107 Doctors Drive, Los Angeles, California-90017 Doctors Drive Doctors Drive 90017

 

[Joe4]

Welcome to the Board!

A few questions about your worksheet:
1. How many columns of data are there?
2. Is there a header/title row? If so, what row(s) are they?
3. For all your rows with data, is there always data in column A?

 

[Joe4]

Let's say that your data is in columns A and B, your header row is row 1, and the column you are looking for these values in column A.

Then you can use this VBA code, which will prompt the user for the value to look for, to delete those rows:

Sub MyDeleteMacro()

    Dim str As String
    Dim lr1 As Long
    Dim lr2 As Long
    Dim rng As Range
    
'   Prompt user what value to look for and delete
    str = InputBox("What string would you like to look for and delete those rows?")
    
'   Find last row with data in column A
    lr1 = Cells(Rows.Count, "A").End(xlUp).Row

'   Create range to filter
    Set rng = Range("A1:B" & lr1)

'   Filter range
    'rng.AutoFilter
    rng.AutoFilter Field:=1, Criteria1:="=*" & str & "*" _
        , Operator:=xlAnd
        
'   Find last row in column A with data after filter
    lr2 = Cells(Rows.Count, "A").End(xlUp).Row

'   Exit sub if no data to delete data (only header visible)
    If lr2 = 1 Then Exit Sub

'   Delete unhidden data
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    
'   Remove filter
    rng.AutoFilter
    
End Sub

 

[nickkmal]

HI, firstly thanks so much for taking the time to reply. Sadly I am only sufficiently proficient to use in-cell calculations / formulas, I dont know anything about VBA code or how to apply it. Is there an in-cell formula you know of?

Thanks again

 

[Joe4]

Excel formulas can only return values to the cells that they are in. They cannot perform action, like the deleting of rows.
So you cannot physically delete rows without VBA, unless you want to do it manually.

The closest thing I think you can use is Filters. With basic Filters, you can hide the rows you do not want (but not delete them).
With Advanced Filters, you can filter the results to a new location (i.e. a new sheet), so you can filter just the rows you want to keep to the new location.

Note that the VBA code should do all you want, and you actually do not need to fully understand to be able to use it.
Here is a short tutorial that shows you exactly where to insert (copy/paste) the code I wrote to place it in your worksheet, and also shows you how to run it:

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

 

[nickkmal]

Thanks so much, will give that a go. very kind of you