Please help

A

Alan Bartley

Sounding desperate....I am :)

I have imported a txt file into excel, however there is a lot of
editing i need
to do manually prior to being able to use it.

Firstly I am looking for a way I can delete all blank rows in one easy
step.

Secondly I would like to delete all rows within Column A containing
text.

Alternatively, for the second step, when the txt file has been
imported into excel, it works out that I need to retain the first 35
rows and delete the next 10 rows. This could go on for over 100
cycles, however the number of times this needs to be performed can
change per each import.

Is there a simple way that I can acheive steps 1 & 2

Would really appreciate some feedback.

Cheers

Alan Bartley
 
D

Don Guillett

Can you just sort it so that the numbers are at the top and delete all at
once below the last number?
 
K

Kevin H. Stecyk

"Alan Bartley" ...
Sounding desperate....I am :)

I have imported a txt file into excel, however there is a lot of
editing i need
to do manually prior to being able to use it.

Firstly I am looking for a way I can delete all blank rows in one easy
step.

Secondly I would like to delete all rows within Column A containing
text.

Alternatively, for the second step, when the txt file has been
imported into excel, it works out that I need to retain the first 35
rows and delete the next 10 rows. This could go on for over 100
cycles, however the number of times this needs to be performed can
change per each import.

Is there a simple way that I can acheive steps 1 & 2

Would really appreciate some feedback.

Cheers

Alan Bartley
Click to expand...

Alan,

Here is a potential solution. It ain't necessarily pretty. This solution
hinges on Col A. It looks at Column A to see if it is blank. And it looks
at Col A to see if it contains text.

To use this solution, go Alt F11. Insert | Module. Paste the VBA code into
the module.

Go to your spreadsheet, and choose Tools | Macro | Macros and choose Delete
Bad Rows.

***Before you do this, make you have a back-up copy of your data.***

Best regards,
Kevin

Sub DeleteBadRows()
Dim rnActiveRegion As Range
Dim rnActiveRegion2 As Range
Dim lRows As Long
Dim lCounter As Long



'\ get data range
Set rnActiveRegion = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
'\ delete blanks
On Error Resume Next
Set rnActiveRegion = rnActiveRegion.SpecialCells(xlBlanks)
rnActiveRegion.EntireRow.Delete
On Error GoTo 0
'\ delete text in col A
Set rnActiveRegion2 = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
lRows = rnActiveRegion2.Rows.Count
For lCounter = lRows To 1 Step -1
If Application.WorksheetFunction.IsText(rnActiveRegion2(lCounter,
1).Value) Then
rnActiveRegion2(lCounter, 1).EntireRow.Delete
End If
Next

End Sub
 
K

Kevin H. Stecyk

Alan Bartley,

If you are having difficulty with my prior solution, just send me an e-mail
at [email protected], and I will try to assist you. It should be farily
quick.

Best regards,
kevin
 
K

Kevin H. Stecyk

Everyone,

Is there a better method to check to see if a cell contains "text" than the
method I used below?

"If Application.WorksheetFunction.IsText(rnActiveRegion2(lCounter,1).Value)
Then"

Thank you.

Best regards,
Kevin
 
K

Kevin H. Stecyk

Hi Debra,

That is much better, isn't it? :)

Thank you for your help!

Best regards,
Kevin


Debra Dalgleish wrote in message...
 
K

Kevin H. Stecyk

Hi Don,

That is also a much better solution.

Thank you!

Best regards,
Kevin
 
H

Harlan Grove

Don Guillett said:
or, how about
if not isnumeric( ....
Click to expand...
....

If the cell contained the formula ="123", would that be text or numeric?
It'd be treated as text in Excel, and the cell's .Value property would be a
string. However, VBA's IsNumeric function would return TRUE.

An alternative that would treat numeric text in cells as text in VBA would
be

If VarType(rnActiveRegion2(lCounter,1).Value) = vbString Then
 
K

Kevin H. Stecyk

Harlan Grove wrote...
If the cell contained the formula ="123", would that be text or numeric?
It'd be treated as text in Excel, and the cell's .Value property would be a
string. However, VBA's IsNumeric function would return TRUE.

An alternative that would treat numeric text in cells as text in VBA would
be

If VarType(rnActiveRegion2(lCounter,1).Value) = vbString Then
Click to expand...

Thank you Harlan. I copied this information too for future reference.

Best regards,
Kevin
 
Top