Hi,

I have a long data file that I am trying to format. There are about
2000 lines in the file, all formatted as text upon import. Many of
the lines are simply lines of text that I am trying to remove, the
goal being to leave behind only the lines that actually contain
numeric data. My thought is to test the first character in each row
to see if it begins with an integer between 1 and 9, inclusive. All
lines meeting this criteria are lines that I want to keep. Any lines
that do not are lines that I want to delete. By looping through the
file, I can fairly quickly clean out all of the excess records. Here
is my code so far:

Dim AcctTest As Variant

'Delete lines that don't contain account balances
Sheets("LYBalances").Range("A1").Select
Do While ActiveCell.Value < ""
AcctTest = (Left(ActiveCell.Value, 1))
AcctTest = AcctTest * 1 'to convert the string into a value
If Not AcctTest < 10 Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop

Using this method, I think that the basic question is how to convert
this single character into a value that can be interpreted
mathmatically. I've searched through the newsgroup, but can't find
anything that I seems to help.

Also, I'm sure that there are other ways to tackle this, so other
suggestions are welcome.

TIA,
Randy Eastland

Hi,

why don't you use Select Case statement


AcctTest = (Left(ActiveCell.Value, 1))
Select Case AcctTest
Case "1" To "9"
'do something
End Select


Haldun


"Randy" , iletide sunu yazdi
om...
Hi,

I have a long data file that I am trying to format. There are about
2000 lines in the file, all formatted as text upon import. Many of
the lines are simply lines of text that I am trying to remove, the
goal being to leave behind only the lines that actually contain
numeric data. My thought is to test the first character in each row
to see if it begins with an integer between 1 and 9, inclusive. All
lines meeting this criteria are lines that I want to keep. Any lines
that do not are lines that I want to delete. By looping through the
file, I can fairly quickly clean out all of the excess records. Here
is my code so far:

Dim AcctTest As Variant

'Delete lines that don't contain account balances
Sheets("LYBalances").Range("A1").Select
Do While ActiveCell.Value < ""
AcctTest = (Left(ActiveCell.Value, 1))
AcctTest = AcctTest * 1 'to convert the string into a value
If Not AcctTest < 10 Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop

Using this method, I think that the basic question is how to convert
this single character into a value that can be interpreted
mathmatically. I've searched through the newsgroup, but can't find
anything that I seems to help.

Also, I'm sure that there are other ways to tackle this, so other
suggestions are welcome.

TIA,
Randy Eastland

Try using the IsNumeric function...

Sub GetNumericRows()
Dim l As Long, lFirst As Long

'find last row
lFirst = Sheets("LYBalances").Range("A65536").End(xlUp).Row

Application.ScreenUpdating = False 'speeds things up

For l = lFirst To 1 Step -1
If Not IsNumeric(Left(Sheets("LYBalances").Cells(l,
1).Formula, 1)) Then _
Sheets("LYBalances").Rows(l).Delete
Next l

End Sub

Cheers,
Dave
-----Original Message-----
Hi,

I have a long data file that I am trying to format.
There are about
2000 lines in the file, all formatted as text upon
import. Many of
the lines are simply lines of text that I am trying to
remove, the
goal being to leave behind only the lines that actually
contain
numeric data. My thought is to test the first character
in each row
to see if it begins with an integer between 1 and 9,
inclusive. All
lines meeting this criteria are lines that I want to
keep. Any lines
that do not are lines that I want to delete. By looping
through the
file, I can fairly quickly clean out all of the excess
records. Here
is my code so far:

Dim AcctTest As Variant

'Delete lines that don't contain account balances
Sheets("LYBalances").Range("A1").Select
Do While ActiveCell.Value < ""
AcctTest = (Left(ActiveCell.Value, 1))
AcctTest = AcctTest * 1 'to convert the string
into a value
If Not AcctTest < 10 Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop

Using this method, I think that the basic question is how
to convert
this single character into a value that can be interpreted
mathmatically. I've searched through the newsgroup, but
can't find
anything that I seems to help.

Also, I'm sure that there are other ways to tackle this,
so other
suggestions are welcome.

TIA,
Randy Eastland
.

Look in help at the val function

? val("abcc")
0
? val("123abc")
123

if val(sStr) 0 then
' keep string
else

--
Regards,
Tom Ogilvy

"Randy" wrote in message
om...
Hi,

I have a long data file that I am trying to format. There are about
2000 lines in the file, all formatted as text upon import. Many of
the lines are simply lines of text that I am trying to remove, the
goal being to leave behind only the lines that actually contain
numeric data. My thought is to test the first character in each row
to see if it begins with an integer between 1 and 9, inclusive. All
lines meeting this criteria are lines that I want to keep. Any lines
that do not are lines that I want to delete. By looping through the
file, I can fairly quickly clean out all of the excess records. Here
is my code so far:

Dim AcctTest As Variant

'Delete lines that don't contain account balances
Sheets("LYBalances").Range("A1").Select
Do While ActiveCell.Value < ""
AcctTest = (Left(ActiveCell.Value, 1))
AcctTest = AcctTest * 1 'to convert the string into a value
If Not AcctTest < 10 Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop

Using this method, I think that the basic question is how to convert
this single character into a value that can be interpreted
mathmatically. I've searched through the newsgroup, but can't find
anything that I seems to help.

Also, I'm sure that there are other ways to tackle this, so other
suggestions are welcome.

TIA,
Randy Eastland