Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if text not in specific format
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if text not in specific format
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if text not in specific format
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if text not in specific format
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if text not in specific format
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with specific text | New Users to Excel | |||
Delete Rows Without Specific Text | Excel Worksheet Functions | |||
Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
Delete Rows With Specific Text | Excel Programming | |||
Delete rows with specific text | Excel Programming |