I'm trying to learn a little macro writing here, I've got some experiec
with other programming languages, but don't know vba very well yet.

I'm probably making a very simple mistake.

All I want to do is select 4 rows, delete them, then skip one and mov
down.

Here is what I have so far, not sure where to go from here.

Dim counter As Integer

For counter = 1 To 2000
Rows(counter, counter + 3).Select
Selection.Delete Shift:=xlUp
Next counter
End Sub

Any help would be appreciated

--
Message posted from http://www.ExcelForum.com

It would be faster to do this:

For counter = 1 To 2000
For x = 1 To 4
Rows(counter).Delete
Next x
Next counte

--
Message posted from http://www.ExcelForum.com

Faster still:

Application.ScreenUpdating = False
For counter = 1 To 2000
For x = 1 To 4
Rows(counter).Delete
Next x
Next counter
Application.ScreenUpdating = True

- Piku

--
Message posted from http://www.ExcelForum.com

And you only need to go to 2000 if you're starting out with 10,00
records because you are deleting rows as you go along. If you hav
2000 to start you should do For x = 1 To 400. - Piku

--
Message posted from http://www.ExcelForum.com

Mecho

you really need to start from the bottom up as your counter will get out of
sync with the "real world" of Excel.

Count up from the bottom, for example:

Sub test()
Application.ScreenUpdating = False
For counter = 1996 To 1 Step -5 ' adjust to match your requirements
Range(Rows(counter), Rows(counter + 3)).EntireRow.Delete
Next counter
Application.ScreenUpdating = True
End Sub


Regards

Trevor

"Mecho " wrote in message
...
I'm trying to learn a little macro writing here, I've got some experiece
with other programming languages, but don't know vba very well yet.

I'm probably making a very simple mistake.

All I want to do is select 4 rows, delete them, then skip one and move
down.

Here is what I have so far, not sure where to go from here.

Dim counter As Integer

For counter = 1 To 2000
Rows(counter, counter + 3).Select
Selection.Delete Shift:=xlUp
Next counter
End Sub

Any help would be appreciated.


---
Message posted from http://www.ExcelForum.com/

Actually, given what you're doing, it is just as effective and easier t
code to start from the top. Generally speaking it is better to go fro
the bottom up, but this is an exception. - Piku

--
Message posted from http://www.ExcelForum.com

Pikus

this may be an exception but I would suggest it is still more logical and
efficient to count up from the bottom. Delete row 1 four times, then move
on to row 2 and delete that 4 times ? You are doing 1600 single row
deletions as opposed to 400 x 4 row deletions. I don't think it is easier
to code if you have to work out that you need to loop 400 times to delete
1600 rows out of 2000.

In fact, putting a simple timer round the code demonstrates how big the
difference can be:

Sub test()
Application.ScreenUpdating = False
Debug.Print Now() & " before tms"
For counter = 1996 To 1 Step -5 ' adjust to match your requirements
Range(Rows(counter), Rows(counter + 3)).EntireRow.Delete
Next counter
Debug.Print Now() & " after tms"
Application.ScreenUpdating = True
End Sub

Sub test2()
Application.ScreenUpdating = False
Debug.Print Now() & " before pikus"
For counter = 1 To 400
For x = 1 To 4
Rows(counter).Delete
Next x
Next counter
Debug.Print Now() & " after pikus"
Application.ScreenUpdating = True
End Sub

04/06/2004 19:26:43 before tms
04/06/2004 19:26:44 after tms
04/06/2004 19:26:51 before pikus
04/06/2004 19:26:56 after pikus
04/06/2004 19:27:00 before tms
04/06/2004 19:27:01 after tms
04/06/2004 19:27:03 before pikus
04/06/2004 19:27:08 after pikus

"Generally speaking it is better to go from the bottom up, but this is an
exception" - maybe so, maybe not ? ;-)

Regards

Trevor


"pikus " wrote in message
...
Actually, given what you're doing, it is just as effective and easier to
code to start from the top. Generally speaking it is better to go from
the bottom up, but this is an exception. - Pikus


---
Message posted from http://www.ExcelForum.com/

You're right, but that's besides the point of which direction you'r
moving. Deleting them one at a time or four at a time is what's makin
the difference here. The reality of the situation is that working i
either direction can be made as fast as the other, but my point wa
that given the simplicity of the task, you gain nothing by the adde
complexity, as simple as it might be to those of us who do things lik
this on a regular basis, of counting in reverse. The diffierence i
minor and one could make a case for either course of action. - Piku

--
Message posted from http://www.ExcelForum.com

I agree with Trevor. Even if yours works, if is not as maintainable or as
easy to change.

But if you want fast

Application.ScreenUpdating = False
Columns("A:A").EntireColumn.Insert
With Range("A1")
.FormulaR1C1 = "=IF(MOD(ROW(),5)<0,""Y"","""")"
.AutoFill Destination:=Range("A1:A2000"), Type:=xlFillDefault
.EntireRow.Insert
End With

Columns("A:A").AutoFilter Field:=1, Criteria1:="Y"
Range("A1:A2001").SpecialCells(xlCellTypeVisible). EntireRow.Delete
Columns("A:A").EntireColumn.Delete

Application.ScreenUpdating = True


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pikus " wrote in message
...
Actually, given what you're doing, it is just as effective and easier to
code to start from the top. Generally speaking it is better to go from
the bottom up, but this is an exception. - Pikus


---
Message posted from http://www.ExcelForum.com/

Well if you change it then it becomes another problem entirely. As
said, in most cases he is correct. This one is exceptionally simpl
and, I assert, simpler from top to bottom. - Piku

--
Message posted from http://www.ExcelForum.com

Mecho

Pikus has rightly pointed out that in this case you could work from the top
down. Others have struggled to get their heads round this approach ...
especially me ... hence the advice that you should approach it from the
bottom upwards.

I guess the main reason that you can adopt the top down approach is that the
pattern is regular. If you were checking for a particular condition and
deleting the row if it is met, you can jump over two or more successive rows
that meet the condition.

Anyway, short story ... if you want the top down version, try this:

Sub test3()
Dim Counter As Long
Dim RangeToDelete As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Debug.Print Now() & " before tms3"
For Counter = 1 To 2000 Step 5 ' adjust to match your requirements
If RangeToDelete Is Nothing Then
Set RangeToDelete = Range(Rows(Counter), Rows(Counter + 3))
Else
Set RangeToDelete = Union(RangeToDelete, Range(Rows(Counter),
Rows(Counter + 3)))
End If
Next 'Counter
If Not RangeToDelete Is Nothing Then
RangeToDelete.EntireRow.Delete
End If
Debug.Print Now() & " after tms3"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Mecho " wrote in message
...
I'm trying to learn a little macro writing here, I've got some experiece
with other programming languages, but don't know vba very well yet.

I'm probably making a very simple mistake.

All I want to do is select 4 rows, delete them, then skip one and move
down.

Here is what I have so far, not sure where to go from here.

Dim counter As Integer

For counter = 1 To 2000
Rows(counter, counter + 3).Select
Selection.Delete Shift:=xlUp
Next counter
End Sub

Any help would be appreciated.


---
Message posted from http://www.ExcelForum.com/