Addressing Question in Excel Macro
 

I'm reducing a 6000 row data set by four fold. I wanted to use the following
macro to delete every three rows, but the String function doesn't seem to
work in a macro to generate a valid row range. Suggestions?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/22/2006 by Bob
'
Row_start = 12
Row_end = Row_start + 2
Do While Row_start < 1600
RS = Str(Row_start)
RE = Str(Row_end)
Address = RS & ":" & RE
Rows(Address).Select
Selection.Delete
Row_start = Row_start + 1
Row_end = Row_end + 1
Loop

End Sub

Addressing Question in Excel Macro
 

I assume your list start at cell A1.
There are quite a number of errors in your original macro. Therefore, I
decided to rewrite it for you. Try the one below:

Sub Macro1()
Dim tmp As Single
Dim tmp2 As Integer
With ActiveSheet.Range("A1").CurrentRegion
tmp2 = .Rows.Count Mod 3
For tmp = .Rows.Count - tmp2 To 3 Step -3
.Rows(tmp).EntireRow.Delete
Next
End With
End Sub


Regards,
Edwin Tam

http://www.vonixx.com



"Bob" wrote:

I'm reducing a 6000 row data set by four fold. I wanted to use the following
macro to delete every three rows, but the String function doesn't seem to
work in a macro to generate a valid row range. Suggestions?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/22/2006 by Bob
'
Row_start = 12
Row_end = Row_start + 2
Do While Row_start < 1600
RS = Str(Row_start)
RE = Str(Row_end)
Address = RS & ":" & RE
Rows(Address).Select
Selection.Delete
Row_start = Row_start + 1
Row_end = Row_end + 1
Loop

End Sub