Auto Delete rows
 

Hi Folks,
Regularly i have to import a text file and convert it into excel format
by using the Data/Get External Data/import text file.

The resulting excel spreadsheet has 4 columns of data.Col A has
numbers,B has text,C & D have numbers.
It is very lengthy which i have to further format in the below manner:
In this spreadsheet after about every 54 rows 8 rows of text appears
which is not useful for me and i have to delete the rows manually.The
text starts from Col B and besides the text in Col A a number appears -
83410.(This number is unique to the text cells and appears only once in
the first row corresponding to the text cells.So below this number in
col B their r 7 lines of text.)

Also,some rows appear which have a sign like -------------.These
appear irregularly in col C & D.
To remove the 8 rows of text and the rows with ------------ i have to
manually delete the rows which becomes a very tedious procedure.
So is it possible to write a macro which will search for no.83410 in
Col A and delete the 8 rows starting from this number(including the
number itself)
Also,the macro shud search for ---------- in col C & D and delete the
entire row corresponding to -----------.
Any help will be very much appreciated.!!


---
Message posted from http://www.ExcelForum.com/

Auto Delete rows
 

One way, adjust to suit ( this looks at all cells after data import having
text in col A )
R
Pete
----------------------------------------------------------------------------
---------------------------------------------------
Sub DELETE_83410()
Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 83410 Then
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 7, numColumns + 0).Select
Selection.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
----------------------------------------------------------------------------
---------------------------------------------------

--
(][ This Email has been scanned by Norton AntiVirus. ][)
"anurag.d " wrote in message
...
Hi Folks,
Regularly i have to import a text file and convert it into excel format
by using the Data/Get External Data/import text file.

The resulting excel spreadsheet has 4 columns of data.Col A has
numbers,B has text,C & D have numbers.
It is very lengthy which i have to further format in the below manner:
In this spreadsheet after about every 54 rows 8 rows of text appears
which is not useful for me and i have to delete the rows manually.The
text starts from Col B and besides the text in Col A a number appears -
83410.(This number is unique to the text cells and appears only once in
the first row corresponding to the text cells.So below this number in
col B their r 7 lines of text.)

Also,some rows appear which have a sign like -------------.These
appear irregularly in col C & D.
To remove the 8 rows of text and the rows with ------------ i have to
manually delete the rows which becomes a very tedious procedure.
So is it possible to write a macro which will search for no.83410 in
Col A and delete the 8 rows starting from this number(including the
number itself)
Also,the macro shud search for ---------- in col C & D and delete the
entire row corresponding to -----------.
Any help will be very much appreciated.!!


---
Message posted from http://www.ExcelForum.com/

Auto Delete rows
 

Pete,
Thanks for ur reply.But it doesnt seem to work

--
Message posted from http://www.ExcelForum.com

Auto Delete rows
 

can u send a copy of the worksheet ?

--
(][ This Email has been scanned by Norton AntiVirus. ][)
"anurag.d " wrote in message
...
Pete,
Thanks for ur reply.But it doesnt seem to work.


---
Message posted from http://www.ExcelForum.com/

Auto Delete rows
 

Anyone else got any better options??

Thanks in advanc

--
Message posted from http://www.ExcelForum.com

Auto Delete rows
 

My Guess is that Col A is text not Number Format, if so you would need to
replace this line

If ActiveCell.Value = 83410 Then

with

If ActiveCell.Value = "83410" Then

it should then be ok

r
pete


Sub DELETE_83410()
Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "83410" Then
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 7, numColumns + 0).Select
Selection.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub


--
(][ This Email has been scanned by Norton AntiVirus. ][)
"anurag.d " wrote in message
...
Anyone else got any better options??

Thanks in advance


---
Message posted from http://www.ExcelForum.com/