VBA to delete rows in certain order

[chongchingsoo]

Dear Sharad Naik,

Further help needed...

I thank u very much for your cool codes. I tried your codes and foun
out that there were still something missing.

I need to delete rows in the following order:-

Row 1 to 7 = 7 rows to delete
Row 57 to 64 = 8 rows to delete
Row 114 to 121 = 8 rows to delete
Row 171 to 178 = 8 rows to delete
Row 228 to 235 = 8 rows to delete
So on and so forth

Therefore, I need to delete 7 rows for the first part, and the res
only need to delete 8 rows.

I shall be highly delighted if u could kindly let me know whether I a
able to add the following sub/codes to the above codes.

Sub autofitcolumn()
Columns("a:l").EntireColumn.AutoFit
End Sub

Thank u very much.

Susan

 

[keepITcool]

Susan..

I think following should do

Sub RowKiller()
Dim rKill As Range, r As Long
With ActiveSheet
With .Range("a1:a" & .Cells(.Rows.Count, 1).End(xlUp).Row)
Debug.Print .Address
Set rKill = .Cells(1, 1).Resize(7)
For r = 57 To .Rows.Count Step 57
Set rKill = Union(rKill, .Cells(r, 1).Resize(8))
Next
End With
rKill.EntireRow.Delete
.Columns("a:l").EntireColumn.AutoFit
End With
End Sub

 

[Sharad Naik]

OK here is the code which delete rows as you want to:

Sub RowDel()
Dim myRowNum As Integer
Dim myRowSet As Integer
Dim myNum As Integer
Dim myMaxRows As Integer
Sheet1.Range("a1:a7").EntireRow.Delete
myRowNum = 50
myMaxRows = 3000 'maximum row number you intend to delete.
'care fully set myMaxRows. 65536 which is max. possible rows, will take
lot of time!!
Do While myRowNum <= myMaxRows
myNum = myRowNum
myRowSet = myRowNum + 7
Do While myNum <= myRowSet
Sheet1.Rows(myRowNum).Delete
myNum = myNum + 1
Loop
myRowNum = myRowNum + 49
Loop
End Sub

Sharad

 

[Sharad Naik]

Susan, keepITcool's code is cool

Sharad

Susan..

I think following should do

Sub RowKiller()
Dim rKill As Range, r As Long
With ActiveSheet
With .Range("a1:a" & .Cells(.Rows.Count, 1).End(xlUp).Row)
Debug.Print .Address
Set rKill = .Cells(1, 1).Resize(7)
For r = 57 To .Rows.Count Step 57
Set rKill = Union(rKill, .Cells(r, 1).Resize(8))
Next
End With
rKill.EntireRow.Delete
.Columns("a:l").EntireColumn.AutoFit
End With
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

chongchingsoo wrote in message