How to Delete Rows in Excel In a Do Loop

[indraneel]

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]

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

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
__________________________

 

[indraneel]

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

 

[indraneel]

hope i detailed enough for you
Regards
Indraneel
otherwise i am online

 

[Richard Buttrey]

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

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
Grappenhall, Cheshire, UK
__________________________

 

[indraneel]

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]

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(xlCellTypeVisible).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

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
Grappenhall, Cheshire, UK
__________________________