MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Deleting Non-consecutive rows in VBA

  Asked By: Rainhard    Date: Mar 07    Category: MS Office    Views: 1754

I have a worksheet with data like this:

A1 1,000.00
A2 Completed
A3 Misc Data
A4 Misc Data
A6 4,500.00
A7 Completed
A8 Misc Data
A9 Misc Data
A11 1,452.25

I want to delete rows A2:A5, keep row A6, delete rows A7:A10, keep row A11,
etc. This process of delete every four rows (and it will ALWAYS be every four
rows) has to be repeated through row A2000.

Is there an eaiser way of recording a macro that will do this than typing in
all the rows I want deleted in one massive range like:
Range("2:5,7:10,11:14...").Select ?



12 Answers Found

Answer #1    Answered By: Jakson Williams     Answered On: Mar 07

I am supposing that the action you want to perform is in the sheet name "Sheet1"
but change to whatever you need.

I would simply try the following

Sub mydelete()
Dim i As Integer
For i = 2 To 2000 Step 4
Next i
End Sub
Hope this helps

Answer #2    Answered By: Abney Martin     Answered On: Mar 07

your solution is the easiest and most direct.

Answer #3    Answered By: Fannie Gonzales     Answered On: Mar 07

It's unbelievable what you can learn in this group and IMHO you can use your new
knowledge very fast...

Answer #4    Answered By: Mason Evans     Answered On: Mar 07

are you sure the code is doing what you expect?

Your code will delete row 2, then delete the sixth row and then the 10th.

The problem is that once you delete the second row, the seventh row is now
the sixth row and is the next to be deleted. When deleted, the 10th row is
actually the original twelfth row.

In deletion, unless you keep track, you should start at the end and work

Answer #5    Answered By: Ludwik Fischer     Answered On: Mar 07

First indeed my code is not doing what was asked for, i.e. delete
rows 2 to 5 and keep row 6, My code is doing just the opposite. I
did not pay enough attention to the question and only read

>This process of delete every four rows  (and it will ALWAYS
> be every four rows) has to be repeated through row A2000

But this is not my question.

Let's say if we were to delete row 2 and then every 4 rows. Then I
guess we do need to delete row 2 and 6 and 10 and 14 , etc... (Which
the code does).
So why go backwards? It is not as if the requirement was to keep
track of every single deletion, update the rows count and then
perform the following deletion.

Am I right or am I completely out...

Answer #6    Answered By: Rayner Fischer     Answered On: Mar 07

The reason to go backward is because if you delete row 5 for example,
then row 10 is no longer row 10, it's row 9. Know what I mean? So you
have to handle that. If you count backwards, you don't have that

Answer #7    Answered By: Adalie Fischer     Answered On: Mar 07

The only reason to do it backwards is if you do not plan to keep track of
the deletions or do not want to try and work out the new end condition.

To delete the 3rd to 6th row you can

For i = 6 to 3 Step -1
Next i


j = 3
For i = 1 to 4
Next i

Which is the same as:


Answer #8    Answered By: Ricardo Smith     Answered On: Mar 07

Let me take another stab at this.

If you delete records in ascending order, that will affect the position of
the following records. If you do it in reverse order, the deletions will not
affect the rows  that come before it.

Answer #9    Answered By: Nora Martin     Answered On: Mar 07

I was definitely rather confused yesterday evening, but this morning everything
cleared up. I got it and hope this time it's for good.

Answer #10    Answered By: Kenny Moore     Answered On: Mar 07

The deletion issue is subtle, it's like trying to hit a moving target.

Answer #11    Answered By: Adalgisa Miller     Answered On: Mar 07

This is how I would probably do it:

Sub DeleteNonContinousRows()

'Place the cursor at the correct cell in the sheet
Application.Goto Reference:="R2C1"

For Counter = 1 To 400 '2000/5
ActiveCell.Offset(1, 0).Range("A1").Select
Next Counter

End Sub

Answer #12    Answered By: Girja Garg     Answered On: Mar 07

If it is just this one spreadsheet and just 2000 rows, it might be
faster to use a spare column to add a repeating sequence of 1 through 5
(as below). This takes a little time, but if you copy the first
sequence and paste, then copy the 2 sequences and paste, then copy the
four sequences and paste, etc...it wouldn't take more than a minute (I
just tried it to be sure).

A1 1,000.00 1
A2 Completed 2
A3 Misc data  3
A4 Misc Data 4
A5 5
A6 4,500.00 1
A7 Completed 2
A8 Misc Data 3
A9 Misc Data 4
A10 5
A11 1,452.25 1

Next, figure out which sequence number corresponds to the data you want
to keep (1 in this case), highlight the whole area, apply the
autofilter, and select the data you want by filtering out the other
sequence numbers. You could also select and delete the rows  you don't
want, but you'd better make a copy first.

Didn't find what you were looking for? Find more on Deleting Non-consecutive rows in VBA Or get search suggestion and latest updates.