Delete certain rows of data...

[RMJ]

I have a number of work sheets containg coordinate data.

data is sorted in the form....

Chainage, Easting, Northing, Elevation,
1609.456 x1 y1 z1
1610 x2 y2 z2
1611.345 x3 y3 z3
1620 x4 y4 z4

ad infinitum, ad nauseum

Can anyone suggest a routine that will delete the entire row of dat
not containing whole numbers in the first column?

Thanks for any help in advance.

Rhodr

 

[Helen Trim]

This code should do the trick:

' Start at the top
Range("A1").Select

' Move down until it finds an empty cell
Do Until ActiveCell = ""
' Check if this cell contains a whole number
If IsNumeric(ActiveCell) Then
If Int(ActiveCell) < ActiveCell Then
' Cell contains a number, but not a whole
number so delete it
ActiveCell.EntireRow.Delete
End If
End If

ActiveCell.Offset(1, 0).Range("A1").Select
Loop

' Return to the top
Range("A1").Select

HTH
Helen

 

[Bernie Deitrick]

Rhodri,

Here's a macro, assuming your values are in column A. If you have a
lot of data, this will be *much* quicker than deleting individual
rows.

Sub DeleteNonIntegerValues()
Range("A1").EntireColumn.Insert
Range("A1").Value = "Keep"
Range("A2").FormulaR1C1 = _
"=IF(RC[1]<>INT(RC[1]),""Trash"",""Keep"")"
Range("A2").Copy Range("A2:A" &
Range("A2").CurrentRegion.Rows.Count)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Range("A65536").End(xlUp)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[RMJ]

Thankyou to both of you for your help - problem now sorted!

: