Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
There may be a more elegant way of doing this, but a simple, non-VBA approach
might be to insert a column next to the column labled "Filename" go to the top line containing a file name and in the new column enter =Right(cellref,3) (where cellref refers to the cell containing the first filename). Copy that formula down to the bottom of your list. Then you could sort on the new column and simply delete the block of rows where the new column has a value of "jpg". "mirdonamy" wrote: I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
Sorry, I misread your problem... but now that I've read a bit more closely, I
realize I don't quite understand. Do you import the text file to Excel and then delete any row with one specified file name? or are you creating a new text file with any file name deleted that is contained in a range contained in a column with a label filename. "mirdonamy" wrote: I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
Assign a range name "Myfiles" (without the quote)
to your 800 files (800 rows by 1 column) then In a standard module paste in the beow code: Sub ElimFileRow() DelItem = Range("C1").Value Set MyRange = Range("Myfiles") For Each cell In MyRange If cell.Value = DelItem Then cell.EntireRow.Delete End If Next cell End Sub In cell C1 -- type in the exact file name you wish to delete, as it appears in the range Myfiles. Run the Macro ElimFileRow HTH "mirdonamy" wrote in message ... I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
Select that column that contains "FileName"
data|filter|autofilter filter to just show those lines to be deleted select those visible cells edit|delete The Data|Filter|autofilter to remove the filter. mirdonamy wrote: I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
How do I assign a range name and what is a standard module (or how do I create one). If this is simple enough to direct me through, I would appreciate it. Otherwise, I am afraid I do not understand what to do here. Sounds very useful though! I would really love to try this! ~Arielle PS. Thank you for your kind response! JMay Wrote: Assign a range name "Myfiles" (without the quote) to your 800 files (800 rows by 1 column) then In a standard module paste in the beow code: Sub ElimFileRow() DelItem = Range("C1").Value Set MyRange = Range("Myfiles") For Each cell In MyRange If cell.Value = DelItem Then cell.EntireRow.Delete End If Next cell End Sub In cell C1 -- type in the exact file name you wish to delete, as it appears in the range Myfiles. Run the Macro ElimFileRow HTH "mirdonamy" wrote in message ... I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
I have a text file with a list of filenames (all .jpg) that I want to delete from my Excel sheet (the entire row, not just the filename). Without looking up each one of the files (listed in the text file) and deleting the row manually, I would like to eliminate the rows containing this filename. I am sure there's a way to automate this! I just don't know how, and I do not know Excel as well as some people here. I don't know macros or anything. I just know Excel better than anyone at my office (which doesn't say much I guess). ~ Arielle SludgeQuake Wrote: Sorry, I misread your problem... but now that I've read a bit more closely, I realize I don't quite understand. Do you import the text file to Excel and then delete any row with one specified file name? or are you creating a new text file with any file name deleted that is contained in a range contained in a column with a label filename. "mirdonamy" wrote: I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
I came up with 1/2 a solution. I added the list of filenames (to the filename row in Excel) and then sorted by filename. Now, everytime there's a partially blank line, I know to delete both (the line above it and the partially blank line). Still, that's 800+ manual deletions. Is there anyway to filter "duplicate" entries of a particular column? I tried filter / custom; but, all I came up with was the ability to filter by something from a drop down list. How do I filter by "duplicate" entry in a certain column? -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
I can't get "Filter for Unique Records" to work properly. I want to delete rows with duplicate filenames. The rows are all unique (as some have ONLY filenames and some have info in the other colums as well). How do I filter or delete rows with duplicate filenames. Unique records does not work, again, as all records are unique. -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows Automatically using a Text File List
How do I assign a range name
Let's say you have information in Range A2:A800. Click on cell A2 and drag (select or highlight) to Cell A800 - then you can (at the menu) click on Insert, Name, Define.. in the top blank box type in Myfiles. That's how you create a range name. click on the down-arrow in the box ABOVE Column A, when you do you will see Myfiles in the list; left click on Myfiles -- suddenly A2:A800 will be selected. what is a standard module? Hold down the Alt key and simultaneously press the F11 key. This will take you into the Visual Basic Editor (a separate program where macros reside). On the menu there, click on Insert, and select Module; This will open a Blank Window.. it is in this window that you should paste the Sub ElimFileRow() ..... (7 line omitted here) End Sub Close the VBE program, this will take you back to The spreadsheet of Excel; In cell C1 - type in a file you wish to delete (it's entire row). Save the Workbook. Press Alt+F8 This will bring up the Macro Window.. Select ElimFileRow from the list (left-click it) and then left click on the button RUN Wa-La,,,,, H(ope) T(his) H(elps) "mirdonamy" wrote in message ... How do I assign a range name and what is a standard module (or how do I create one). If this is simple enough to direct me through, I would appreciate it. Otherwise, I am afraid I do not understand what to do here. Sounds very useful though! I would really love to try this! ~Arielle PS. Thank you for your kind response! JMay Wrote: Assign a range name "Myfiles" (without the quote) to your 800 files (800 rows by 1 column) then In a standard module paste in the beow code: Sub ElimFileRow() DelItem = Range("C1").Value Set MyRange = Range("Myfiles") For Each cell In MyRange If cell.Value = DelItem Then cell.EntireRow.Delete End If Next cell End Sub In cell C1 -- type in the exact file name you wish to delete, as it appears in the range Myfiles. Run the Macro ElimFileRow HTH "mirdonamy" wrote in message ... I have a text file list of .jpg files that I want to delete (rows) from Excel. The list has 800+ items in it. Deleting these rows one at a time in Excel (after doing a "find") is terribly time consuming. Does anyone know how to make Excel read my text file and delete the rows with that filename in it (automatically). The filename (listed in the text file) is in a column (in my excel sheet) called "filename". Any help would be most appreciated!!! Seriously! I may have to spend hours on this otherwise! Thank you, Arielle -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500059 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling Text from Another File | Excel Worksheet Functions | |||
how can i convert text file to excel file ? | New Users to Excel | |||
Importing text file to excel | Excel Discussion (Misc queries) | |||
Importing a text file into Excel with too many rows? | Excel Discussion (Misc queries) | |||
how i make a pgm in excel for copying all the rows to a new file . | Excel Discussion (Misc queries) |