VBA to delete rows in certain order
 

Dear Sharad Naik and keepITcool,

Thousand thanks to both of your codes. Both work well. Two thumbs up
:)

Frankly speaking, both of u is intelligence people because, both of
can come out those codes in such a short time frame.

Anyway, thanks again.

Special cool gift to both of u :cool:

"The most rewarding things you do in life are often the
ones that look like they cannot be done." -by Arnold Palmer


Susan



Sharad Naik Wrote:
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 an
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
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 followin
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" wrot
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