Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select fields with more then one dash (-)
I have 40,000 records and need to delete the ones that contain more then one
dash. For example: 1 k-90 2 k-90-1 3 k-50 4 k-50-1 5 k-50-1-a I need to select fields 2,4, and 5 so I can get rid of them Any advice on how to do this? thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select fields with more then one dash (-)
Assuming the data is in column B and there are no blank lines and the format
of the data is consistent then the following should work. Sub DeleteRows() Dim inputcell As Range Dim nextcell As Range Set inputcell = Worksheets(1).Range("B2") Do Until IsEmpty(inputcell.Value) Set nextcell = inputcell.Offset(1) If inputcell.Value Like "?-??-*" Then inputcell.EntireRow.Delete End If Set inputcell = nextcell Loop End Sub "Knox" wrote: I have 40,000 records and need to delete the ones that contain more then one dash. For example: 1 k-90 2 k-90-1 3 k-50 4 k-50-1 5 k-50-1-a I need to select fields 2,4, and 5 so I can get rid of them Any advice on how to do this? thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select fields with more then one dash (-)
Give this a whirl...
Sub DeleteMultiDash() Dim rngToSearch As Range Dim rngToDelete As Range Dim rng As Range With Sheets("Sheet1") Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) End With For Each rng In rngToSearch If Len(rng) = Len(Replace(rng, "-", "")) + 2 Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rng, rngToDelete) End If End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "Knox" wrote: I have 40,000 records and need to delete the ones that contain more then one dash. For example: 1 k-90 2 k-90-1 3 k-50 4 k-50-1 5 k-50-1-a I need to select fields 2,4, and 5 so I can get rid of them Any advice on how to do this? thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select fields with more then one dash (-)
yea that worked real well! thanx
"Jim Thomlinson" wrote: Give this a whirl... Sub DeleteMultiDash() Dim rngToSearch As Range Dim rngToDelete As Range Dim rng As Range With Sheets("Sheet1") Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) End With For Each rng In rngToSearch If Len(rng) = Len(Replace(rng, "-", "")) + 2 Then If rngToDelete Is Nothing Then Set rngToDelete = rng Else Set rngToDelete = Union(rng, rngToDelete) End If End If Next rng If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End Sub -- HTH... Jim Thomlinson "Knox" wrote: I have 40,000 records and need to delete the ones that contain more then one dash. For example: 1 k-90 2 k-90-1 3 k-50 4 k-50-1 5 k-50-1-a I need to select fields 2,4, and 5 so I can get rid of them Any advice on how to do this? thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I replace/select new fields in pivot table using a macro? | Excel Discussion (Misc queries) | |||
Not able to select Calculated Fields for Pivot Tables | Excel Worksheet Functions | |||
Tabbing to select fields | Excel Worksheet Functions | |||
Since SP2, my pivot tables cannot select various fields | Excel Discussion (Misc queries) | |||
understanding dash dash in a excel formula | Excel Worksheet Functions |