Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Delete rows
Pete,
Thanks for ur reply.But it doesnt seem to work -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Delete rows
Anyone else got any better options??
Thanks in advanc -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get old rows to auto delete when new are added? | Excel Discussion (Misc queries) | |||
How do you format an auto update to delete rows with expired date | Excel Worksheet Functions | |||
Auto Number the Rows of Auto Filter Result | Excel Discussion (Misc queries) | |||
If formula result is false, how do I auto-delete that rows? | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions |