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




Sharad Naik Wrote:
Hello Susan,

Row 1 to 7 : 7 - 1 = 6 so 7 rows to delete
Row 57 to 64 64 - 57 = 7 so 8 rows to delete

So I assume you meant Row 57 to Row 63, and then Row 113 to 119, 16
to
175 and so on etc.. ?
Please check your seires.

Assuming the series as i mentioned above, you can use following code:

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

Sharad

"chongchingsoo" wrote in
message ...

I would like to delete rows in certain order in a MS Excel worksheet
using VBA ONCE:-

Row 1 to 7
Row 57 to 64
Row 114 to 121
Row 171 to 178
Row 228 to 235
Row 285 to 292
Till the last row of the worksheet.
Any help?

Thank you


Susan


--
chongchingsoo

------------------------------------------------------------------------
chongchingsoo's Profile:
http://www.excelforum.com/member.php...o&userid=16338
View this thread
http://www.excelforum.com/showthread...hreadid=277271


--
chongchingso
-----------------------------------------------------------------------
chongchingsoo's Profile: http://www.excelforum.com/member.php...fo&userid=1633
View this thread: http://www.excelforum.com/showthread.php?threadid=27727

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
:


Dear Sharad Naik,

Further help needed... ;)

I thank u very much for your cool codes. I tried your codes and found
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 rest
only need to delete 8 rows.

I shall be highly delighted if u could kindly let me know whether I am
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




Sharad Naik Wrote:
Hello Susan,

Row 1 to 7 : 7 - 1 = 6 so 7 rows to delete
Row 57 to 64 64 - 57 = 7 so 8 rows to delete

So I assume you meant Row 57 to Row 63, and then Row 113 to 119,
169
to 175 and so on etc.. ?
Please check your seires.

Assuming the series as i mentioned above, you can use following code:

Sub RowDel()
Dim myRowNum As Integer
Dim myRowSet As Integer
Dim myNum As Integer
Dim myMaxRows As Integer
myRowNum = 1
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 + 6
Do While myNum <= myRowSet
Sheet1.Rows(myRowNum).Delete
myNum = myNum + 1
Loop
myRowNum = myRowNum + 49
Loop
End Sub

Sharad

"chongchingsoo" wrote in
message ...

I would like to delete rows in certain order in a MS Excel
worksheet
using VBA ONCE:-

Row 1 to 7
Row 57 to 64
Row 114 to 121
Row 171 to 178
Row 228 to 235
Row 285 to 292
Till the last row of the worksheet.
Any help?

Thank you


Susan


--
chongchingsoo


------------------------------------------------------------------------
chongchingsoo's Profile:
http://www.excelforum.com/member.php...o&userid=16338
View this thread:
http://www.excelforum.com/showthread...hreadid=277271

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

"chongchingsoo" wrote in
message ...

Dear Sharad Naik,

Further help needed... ;)

I thank u very much for your cool codes. I tried your codes and found
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 rest
only need to delete 8 rows.

I shall be highly delighted if u could kindly let me know whether I am
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




Sharad Naik Wrote:
Hello Susan,

Row 1 to 7 : 7 - 1 = 6 so 7 rows to delete
Row 57 to 64 64 - 57 = 7 so 8 rows to delete

So I assume you meant Row 57 to Row 63, and then Row 113 to 119, 169
to
175 and so on etc.. ?
Please check your seires.

Assuming the series as i mentioned above, you can use following code:

Sub RowDel()
Dim myRowNum As Integer
Dim myRowSet As Integer
Dim myNum As Integer
Dim myMaxRows As Integer
myRowNum = 1
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 + 6
Do While myNum <= myRowSet
Sheet1.Rows(myRowNum).Delete
myNum = myNum + 1
Loop
myRowNum = myRowNum + 49
Loop
End Sub

Sharad

"chongchingsoo" wrote in
message ...

I would like to delete rows in certain order in a MS Excel worksheet
using VBA ONCE:-

Row 1 to 7
Row 57 to 64
Row 114 to 121
Row 171 to 178
Row 228 to 235
Row 285 to 292
Till the last row of the worksheet.
Any help?

Thank you


Susan


--
chongchingsoo

------------------------------------------------------------------------
chongchingsoo's Profile:
http://www.excelforum.com/member.php...o&userid=16338
View this thread:
http://www.excelforum.com/showthread...hreadid=277271



--
chongchingsoo
------------------------------------------------------------------------
chongchingsoo's Profile:
http://www.excelforum.com/member.php...o&userid=16338
View this thread: http://www.excelforum.com/showthread...hreadid=277271

Susan, keepITcool's code is cool :)

Sharad

"keepITcool" wrote in message
...
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
:


Dear Sharad Naik,

Further help needed... ;)

I thank u very much for your cool codes. I tried your codes and found
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 rest
only need to delete 8 rows.

I shall be highly delighted if u could kindly let me know whether I am
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




Sharad Naik Wrote:
Hello Susan,

Row 1 to 7 : 7 - 1 = 6 so 7 rows to delete
Row 57 to 64 64 - 57 = 7 so 8 rows to delete

So I assume you meant Row 57 to Row 63, and then Row 113 to 119,
169
to 175 and so on etc.. ?
Please check your seires.

Assuming the series as i mentioned above, you can use following code:

Sub RowDel()
Dim myRowNum As Integer
Dim myRowSet As Integer
Dim myNum As Integer
Dim myMaxRows As Integer
myRowNum = 1
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 + 6
Do While myNum <= myRowSet
Sheet1.Rows(myRowNum).Delete
myNum = myNum + 1
Loop
myRowNum = myRowNum + 49
Loop
End Sub

Sharad

"chongchingsoo" wrote in
message ...

I would like to delete rows in certain order in a MS Excel
worksheet
using VBA ONCE:-

Row 1 to 7
Row 57 to 64
Row 114 to 121
Row 171 to 178
Row 228 to 235
Row 285 to 292
Till the last row of the worksheet.
Any help?

Thank you


Susan


--
chongchingsoo


------------------------------------------------------------------------
chongchingsoo's Profile:
http://www.excelforum.com/member.php...o&userid=16338
View this thread:
http://www.excelforum.com/showthread...hreadid=277271