Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
hello
i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
I assume you've commented out the Selection.delete whilst testing it.
The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
hello Richard
i have data such as Element ID SMAx Smin etc etc (All these being headers ) Then I have data I get my analysis output in a textpad file from where i import it It imports the Headers at every page end This is what i want to delete and make the data a continuous one I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of data per run And Yes I had commeneted out the selection.delete while testing Thank you for the help and i thought on th same lines that the program once it deletes the lines doesnot recognize the r2 set a question will it help if i say r2 as a relative reference (dont think that is going to help) Thank you and Regards Indraneel Richard Buttrey wrote: I assume you've commented out the Selection.delete whilst testing it. The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
hope i detailed enough for you
Regards Indraneel otherwise i am online indraneel wrote: hello Richard i have data such as Element ID SMAx Smin etc etc (All these being headers ) Then I have data I get my analysis output in a textpad file from where i import it It imports the Headers at every page end This is what i want to delete and make the data a continuous one I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of data per run And Yes I had commeneted out the selection.delete while testing Thank you for the help and i thought on th same lines that the program once it deletes the lines doesnot recognize the r2 set a question will it help if i say r2 as a relative reference (dont think that is going to help) Thank you and Regards Indraneel Richard Buttrey wrote: I assume you've commented out the Selection.delete whilst testing it. The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
Hi,
No, making r2 relative won't make any difference since the problem is that you are deleting the r2 range completely. I don't fully understand your data and where the lines you want to delete appear, but as a minimum you'll need to re Set range r2 each time you go through the loop. Hence the Set r2 = ActiveSheet.Rows("46:52") needs to be inside the loop, but of course you'll need to work out and build in which rows are relevant each time, since 46:52 each time apart from the first pass, will no doubt be incorrect. I have a lot of similar macros, where a .prn or .txt file that's imported from another application, needs to have all sorts of banner headings, column headings, footers and surplus format lines, deleted to end up with a neat flat database. After adopting several methids, the approach I've found best is as follows. 1. Import the data 2. Add a helper column A 3. Work out an Excel formula that will identify whether a particular row is one you want to keep or not. e.g in A1 =If(and("B1"<"", F1<"----")."Keep","Delete") and have the macro put this in A1 4. Now copy this formula down column A for the whole database 5. Filter the database on column A for the word "Delete") 6. Delete all the filtered rows 7 Finally unfilter the database, delete column A and Bob's your Uncle (or whatever is your local equivalent of Job done!) HTH On 14 Sep 2006 10:15:04 -0700, "indraneel" wrote: hello Richard i have data such as Element ID SMAx Smin etc etc (All these being headers ) Then I have data I get my analysis output in a textpad file from where i import it It imports the Headers at every page end This is what i want to delete and make the data a continuous one I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of data per run And Yes I had commeneted out the selection.delete while testing Thank you for the help and i thought on th same lines that the program once it deletes the lines doesnot recognize the r2 set a question will it help if i say r2 as a relative reference (dont think that is going to help) Thank you and Regards Indraneel Richard Buttrey wrote: I assume you've commented out the Selection.delete whilst testing it. The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
hi Richard
i got your point but cant think of a formula because out of the lines i have to delete there are some that have a entry in each and every cell can i say something like if there is an entry in each cell from a to s delete but again i have opthers that have only 1 or 2 cells filled which i want to delete help!!!! regards indraneel Richard Buttrey wrote: Hi, No, making r2 relative won't make any difference since the problem is that you are deleting the r2 range completely. I don't fully understand your data and where the lines you want to delete appear, but as a minimum you'll need to re Set range r2 each time you go through the loop. Hence the Set r2 = ActiveSheet.Rows("46:52") needs to be inside the loop, but of course you'll need to work out and build in which rows are relevant each time, since 46:52 each time apart from the first pass, will no doubt be incorrect. I have a lot of similar macros, where a .prn or .txt file that's imported from another application, needs to have all sorts of banner headings, column headings, footers and surplus format lines, deleted to end up with a neat flat database. After adopting several methids, the approach I've found best is as follows. 1. Import the data 2. Add a helper column A 3. Work out an Excel formula that will identify whether a particular row is one you want to keep or not. e.g in A1 =If(and("B1"<"", F1<"----")."Keep","Delete") and have the macro put this in A1 4. Now copy this formula down column A for the whole database 5. Filter the database on column A for the word "Delete") 6. Delete all the filtered rows 7 Finally unfilter the database, delete column A and Bob's your Uncle (or whatever is your local equivalent of Job done!) HTH On 14 Sep 2006 10:15:04 -0700, "indraneel" wrote: hello Richard i have data such as Element ID SMAx Smin etc etc (All these being headers ) Then I have data I get my analysis output in a textpad file from where i import it It imports the Headers at every page end This is what i want to delete and make the data a continuous one I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of data per run And Yes I had commeneted out the selection.delete while testing Thank you for the help and i thought on th same lines that the program once it deletes the lines doesnot recognize the r2 set a question will it help if i say r2 as a relative reference (dont think that is going to help) Thank you and Regards Indraneel Richard Buttrey wrote: I assume you've commented out the Selection.delete whilst testing it. The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Delete Rows in Excel In a Do Loop
Hi,
Yes you could easily do something to identify that condition. Add the new column A as I suggested, so that now you're trying to check Columns B to T Now in A1 have the macro enter =IF(COUNTA(B1:T1)<19,"Keep","Delete") and copy down column A as appropriate Provided every cell in a row contains an entry, then CountA will evaluate to 19 and the IF will return the value "Delete" You can now filter on Column A and delete all the relevant rows with something like Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible).EntireRow.Delete I'd advise also first creating an additional row 1 with the word "Keep" in A1 so that when you filter, the filter doesn't start with the first row of data in case this should happen to be a "Keep" row. That's because the filter headings remain visible and will be selected by the currentregion command and deleted. HTH On 14 Sep 2006 10:57:02 -0700, "indraneel" wrote: hi Richard i got your point but cant think of a formula because out of the lines i have to delete there are some that have a entry in each and every cell can i say something like if there is an entry in each cell from a to s delete but again i have opthers that have only 1 or 2 cells filled which i want to delete help!!!! regards indraneel Richard Buttrey wrote: Hi, No, making r2 relative won't make any difference since the problem is that you are deleting the r2 range completely. I don't fully understand your data and where the lines you want to delete appear, but as a minimum you'll need to re Set range r2 each time you go through the loop. Hence the Set r2 = ActiveSheet.Rows("46:52") needs to be inside the loop, but of course you'll need to work out and build in which rows are relevant each time, since 46:52 each time apart from the first pass, will no doubt be incorrect. I have a lot of similar macros, where a .prn or .txt file that's imported from another application, needs to have all sorts of banner headings, column headings, footers and surplus format lines, deleted to end up with a neat flat database. After adopting several methids, the approach I've found best is as follows. 1. Import the data 2. Add a helper column A 3. Work out an Excel formula that will identify whether a particular row is one you want to keep or not. e.g in A1 =If(and("B1"<"", F1<"----")."Keep","Delete") and have the macro put this in A1 4. Now copy this formula down column A for the whole database 5. Filter the database on column A for the word "Delete") 6. Delete all the filtered rows 7 Finally unfilter the database, delete column A and Bob's your Uncle (or whatever is your local equivalent of Job done!) HTH On 14 Sep 2006 10:15:04 -0700, "indraneel" wrote: hello Richard i have data such as Element ID SMAx Smin etc etc (All these being headers ) Then I have data I get my analysis output in a textpad file from where i import it It imports the Headers at every page end This is what i want to delete and make the data a continuous one I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of data per run And Yes I had commeneted out the selection.delete while testing Thank you for the help and i thought on th same lines that the program once it deletes the lines doesnot recognize the r2 set a question will it help if i say r2 as a relative reference (dont think that is going to help) Thank you and Regards Indraneel Richard Buttrey wrote: I assume you've commented out the Selection.delete whilst testing it. The reason you get the error message is that once you delete the range r2, that's it. VBA no longer has a range r2 anymore, hence next time round the loop when it tries to select r2, it complains. If you explain in a little more deatil what you're trying to achieve, no doubt we can help. Rgds On 14 Sep 2006 07:50:35 -0700, "indraneel" wrote: hello i need some help with deleting Rows in excel using a do loop my program looks like this Sub Cleanup() Dim r1 As Range Dim r2 As Range Set r1 = Cells(6, 2) Set r2 = ActiveSheet.Rows("46:52") Do While r1.Value < "" r2.Select 'Selection.Delete Shift:=xlUp r2.Font.Bold = True Set r1 = r1.Offset(47, 0) Set r2 = r2.Offset(47, 0) Loop End Sub The problem i am having is when i make the things i want to delete bold they do become bold but when i want to delete those rows it does for the first row and then stops and says "OBJECT REQUIRED" i known i make making a mistake in the code somewhere but not getting where thank you in advance for your help/advice Regards Indraneel __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find and delete all empty rows in an excel worksheet | Excel Worksheet Functions | |||
Excel Novice: Delete blank rows | Excel Discussion (Misc queries) | |||
I WANT TO DELETE A WIDE OF DATE IN A EXCEL FILE | Excel Discussion (Misc queries) | |||
How do I delete columns and rows in Excel, not just hide them | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |