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!

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!

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!

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!