delete row if text not in specific format

[Abdul]

Hi,

I have data in my column B

i want to delete all rows which are not in the specific format
00.000000.0000000.00.000.0000.0000

(zeros with any number)

thanks

 

[Gary''s Student]

Try this:

Sub CleanUp()
Dim n As Long, i As Long, s As String
n = Cells(Rows.Count, "B").End(xlUp).Row
s = "00.000000.0000000.00.000.0000.0000"
For i = n To 1 Step -1
With Cells(i, "B")
If .NumberFormat <> s Then
.EntireRow.Delete
End If
End With
Next
End Sub

 

[ryguy7272]

Make a backup of your file before you run this in case it does something you
don't expect.

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "<>*00.000000.0000000.00.000.0000.0000*"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub

HTH,
Ryan---

 

[Rick Rothstein]

Assuming you did **not** mean the cells were Custom Formatted with the
number format you showed; but rather there were multiple type entries in
Column B and that you only wanted to preserve rows whose Column B cells
contained entries that looked like you pattern, then try this macro...

Sub RemoveNumbers()
Dim X As Long, LastRow As Long
Const StartRow As Long = 2
Const DataColumn As String = "B"
Const Pattern As String = "##.######.#######.##.###.####.####"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = LastRow To StartRow Step -1
If Not .Cells(X, DataColumn).Value Like Pattern Then
.Cells(X, DataColumn).EntireRow.Delete
End If
Next
End With
End Sub

As with all macros, you should test this out on a copy of your data since
you **cannot** Undo worksheet changes produced by VB code.

 

[Rick Rothstein]

I guess you could not have Custom Formatted the cells to look like your
pattern as there would be no way to enter numbers large enough to fill the
pattern, so the coded solution I offered must be what you were looking for.
Here is a minor revision to hide the process so the user doesn't have to
watch each row being deleted (which should make the code more efficient as
well)...

Sub RemoveRowsWithPattern()
Dim X As Long, LastRow As Long
Const StartRow As Long = 2
Const DataColumn As String = "B"
Const Pattern As String = "##.######.#######.##.###.####.####"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow Step -1
If Not .Cells(X, DataColumn).Value Like Pattern Then
.Cells(X, DataColumn).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End With
End Sub