Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Greetings,
I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Start here Solon
http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Ron,
Thanks for the info, wow that's a hefty bit of macro, isn't it? Since I'm trying to learn more of this.. I played around a bit and came up with this: Dim FoundCell As Range Dim myRng As Range Set myRng = Range("D:D") For Each D In myRng Set FoundCell = myRng.Find("Desired Text Here") If D.Value = ("Desired Text Here") Then FoundCell.EntireRow.Delete Next It works pretty well, but for some reason, only works on a few hundred rows, then stops. I can run it a number of times in a row to clear everything out, but am wondering why it will only work for so long, then stop. Any ideas?? Thanks, Solon "Ron de Bruin" wrote in message ... Start here Solon http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
For a Find macro see
http://www.rondebruin.nl/delete.htm#Find -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... Ron, Thanks for the info, wow that's a hefty bit of macro, isn't it? Since I'm trying to learn more of this.. I played around a bit and came up with this: Dim FoundCell As Range Dim myRng As Range Set myRng = Range("D:D") For Each D In myRng Set FoundCell = myRng.Find("Desired Text Here") If D.Value = ("Desired Text Here") Then FoundCell.EntireRow.Delete Next It works pretty well, but for some reason, only works on a few hundred rows, then stops. I can run it a number of times in a row to clear everything out, but am wondering why it will only work for so long, then stop. Any ideas?? Thanks, Solon "Ron de Bruin" wrote in message ... Start here Solon http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Dim t As Range
Dim D As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1").Value Then D.Entirerow.Delete End If Next Gord Dibben MS Excel MVP On Fri, 7 Dec 2007 14:27:46 -0500, "Solon" wrote: Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
AHHH.. the "D" before "Entirerow".. I WONDERED what was causing the
trouble.. I'm sort of getting a grasp of this. Thanks, Solon "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Dim t As Range Dim D As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1").Value Then D.Entirerow.Delete End If Next Gord Dibben MS Excel MVP On Fri, 7 Dec 2007 14:27:46 -0500, "Solon" wrote: Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Note: When you delete you must start at the bottom row and go up
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... AHHH.. the "D" before "Entirerow".. I WONDERED what was causing the trouble.. I'm sort of getting a grasp of this. Thanks, Solon "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Dim t As Range Dim D As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1").Value Then D.Entirerow.Delete End If Next Gord Dibben MS Excel MVP On Fri, 7 Dec 2007 14:27:46 -0500, "Solon" wrote: Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Macro for deleting rows
Yes, I'm learning THAT the hard way, too :-)
"Ron de Bruin" wrote in message ... Note: When you delete you must start at the bottom row and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Solon" wrote in message ... AHHH.. the "D" before "Entirerow".. I WONDERED what was causing the trouble.. I'm sort of getting a grasp of this. Thanks, Solon "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Dim t As Range Dim D As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1").Value Then D.Entirerow.Delete End If Next Gord Dibben MS Excel MVP On Fri, 7 Dec 2007 14:27:46 -0500, "Solon" wrote: Greetings, I'm using Excel 2003 to do some deleting and sorting of a monthly list and I'd like to automate most, if not all, of it. Ideally, I'd want Excel to search through column D for a certain text entry and delete any row that contains this text. I would also like it to only search rows with data (anywhere from 50 to about 3,000), but I'm sure searching the entire column won't be a big deal. My difficulty seems, at this point, to be that I can't get the row to delete. When I ran the macro, it deleted ALL rows, so I changed it to Rows.Select and it selected ALL rows. I can't seem to get Row.Delete (or Row.Select) to work on just the row where the entry was found. Here's what I've got so far: Dim t As Range Set t = Range("D2:D3000") For Each D In t If D.Value = Worksheets("Sheet2").Range("A1") Then Rows.Delete Next It works, if I want to delete ALL rows (Select and Activate work as well). I've never tried to get Excel to go to the last row with data and somehow 'record' the row number, much less use that to set a range, but I'd really like to find a way to delete just one row based on the content of one of its cells. Any ideas? Thanks, Solon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - deleting trailing empty rows | New Users to Excel | |||
Subtotals Deleting Rows after removing Subtotal - 2003 Excel | Excel Worksheet Functions | |||
Microsoft Excel 2003 - Deleting rows | Excel Discussion (Misc queries) | |||
Deleting Rows in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003; spreadsheet with filtering; deleting rows | Excel Worksheet Functions |