0

I have written the below macros below, and it works perfect when there is data (example) 12345 on the SMT02 sheet.

So if 12345 is present, it leaves those rows and deletes the rest of the rows with other data.

But I want it to work in such a way, even if 12345 is not present, then I still want everything else (rows) to be deleted. At the moment it debugs and stops. Can anyone help please?

Dim c As Range
Dim SrchRng

Sheets("SMT02").Select
Range("B1").Select
Set SrchRng = ActiveSheet.Range("B1", ActiveSheet.Range("B65536").End(xlUp))
Do
    Set c = SrchRng.Find("12345", LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
pnuts
  • 58,317
  • 11
  • 87
  • 139
kpatel
  • 1
  • 1
  • 1
  • Could you say what it is you want to do? We can't figure out what you want from your code if we're fixing mistakes in your code. – rakslice Aug 28 '14 at 04:57
  • I want to delete all the rows that does not contain "12345" and keep only the rows with "12345" But - if the spreadsheet data that came to us, has no "12345", it should still delete all the rest of the rows which does not have "12345" Basically, the sheet should be blank after delete (if "12345" not present). Hope I have explained correctly. Thank you. – kpatel Aug 28 '14 at 05:26
  • Use [AUTOFILTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s)? – Siddharth Rout Aug 28 '14 at 06:27

1 Answers1

1

If you are trying to delete each row in a worksheet when the value "12345" is not present in the cells of a specified column (column B in your example) on a specific sheet (SMT02 in your example) I think you will find that this works:

Sub DeleteRowIfNot12345()

    Dim Rng As Range
    Dim x As Long

    Sheets("SMT02").Select

    Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
    For x = Rng.Rows.Count To 1 Step -1
        If InStr(1, Rng.Cells(x, 1).Value, "12345") = 0 Then
            Rng.Cells(x, 1).EntireRow.Delete
        End If
    Next x

End Sub
sallyapplepie
  • 11
  • 2
  • Thanks. But if I use this above, it does not delete anything / any row. It just sits there doing nothing. – kpatel Aug 28 '14 at 22:08
  • I looked at it again and tested it but cannot get it to fail unless the Sheet name is different. Here is how I have it set up: https://flic.kr/p/oXkM5M – sallyapplepie Aug 29 '14 at 03:23
  • Works beautifully, and I had tried tons of solutions before, each time with a different problem. – Vera Oct 24 '20 at 21:40