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

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

--
Gary''s Student - gsnu200860


"Abdul" wrote:

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

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Abdul" wrote:

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

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 (MVP - Excel)


"Abdul" <(E-Mail Removed)> wrote in message
news:238c5984-63fc-4c41-9430-(E-Mail Removed)...
> 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

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

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 (MVP - Excel)
>
>
> "Abdul" <(E-Mail Removed)> wrote in message
> news:238c5984-63fc-4c41-9430-(E-Mail Removed)...
>> 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
>