So if you format the cells as 0.0 and use .text, does the code work ok?

Or you could check to see if the value is a whole number:
If int(wks.Cells(i, 1).value) = wks.cells(i,1).value then


D.Parker wrote:

My apologies for any confusion. What happens is the following.

1) I open a .txt file in Excel.
2) The first column of data I format the numbers in the column to 1 decimal
place
ex. 1.23568 goes to 1.2
1.0235 goes to 1.0 and so on.
3) I have to go back and change the values to "Precision As Displayed" to
TRY and retain
the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on.
4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3
...n.
5) When I run the algorithm the everything on my sheet is deleted.
6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the
algorithm work accordingly.

Thus, my question was how to you keep the decimal and zero when you change
the values to text keeping the Precision As Displayed? Otherwise, I have to
embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the
format 1.0, 2.0, 3.0.

Or can the algorithm be changed to check for whole numbers in text format
instead of "*.0"? Whichever method is easiest is preferred.

After I manually manipulate the fields and the algorithm runs, the second
problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out
the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0,
2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n.

I hope this helps you to help me and thank you for you time.

Kind regards,

D.Parker

"Dave Peterson" wrote:

If you want to look at the way the data is formatted in the cell, try changing:

If Not wks.Cells(i, 1).Value Like "*.0" Then
to
If Not wks.Cells(i, 1).Text Like "*.0" Then

But I'm confused about the duplicates and the changes in formatting that you've
described in the other posts.

D.Parker wrote:

Gord, I tried this and the same result when I ran the code. Everything on my
sheet was deleted, event my command button.

Just to clarify, I am opening a text file that is space, comma delimited and
thus the numbers in the column appear in the form 0.0000, 0.345, 1.2345,
2.987...and so on. I format the column with the numbers to only 1 decimal
place such that I can purge out any data that does not appear in the form
0.0, 1.0, 2.0, 3.0 and so on. After this, if I have duplicate rows of 0.0,
0.0, 1.0, 1.0, 2.0, 2.0 and so on one row from each interger must be deleted
so I end up with 0.0, 1.0, 2.0, 3.0, etc. I hope this clears up any
ambiguity.

I'm still puzzled why everything on the sheet gets deleted.

Kind regards,

D.Parker

"Gord Dibben" wrote:

Is the data text or numeric?

You originally stated you opened a text file so I assumed the data was text.

The macro looks for text data and deletes any rows that do not contain *.0

When you open the *.txt file and the wizard pops, hit NextNextColumn Data
Format. Check "Text" and Finish.

Now the macro should work.


Gord

On Tue, 9 Oct 2007 19:15:04 -0700, D.Parker
wrote:

I ran this and it deleted "everything" on my sheet. I was looking for the
following:

1.0
2.0
3.0
4.0

and so on. Can you please re-advise?

Kind regards,

D.Parker

"Gord Dibben" wrote:

Sub Delete_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer
Dim Collet As String
Dim whatwant As String
Set wks = ActiveSheet
Application.ScreenUpdating = False

iLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Not wks.Cells(i, 1).Value Like "*.0" Then
wks.Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True

End Sub


Gord Dibben MS Excel MVP

On Tue, 9 Oct 2007 14:10:01 -0700, D.Parker
wrote:

Hello! I would greatly appreciate your expertise on opening up a text file
and deleting rows of data if they do not meet the format of "0.0", "1.0",
"2.0", etc. After that delete any empty rows and shift the rows up to form a
consecutive numbering sequence in column A. The order of operations should
not matter as long as it is efficient, the total number of rows I'm working
with is ~7K. Your time and expertise is greatly appreciated.

Kind regards,

D.Parker





--

Dave Peterson


--

Dave Peterson