|
VBA to delete rows in certain order
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 |
VBA to delete rows in certain order
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 |
VBA to delete rows in certain order
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 |
VBA to delete rows in certain order
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 |
| All times are GMT +1. The time now is 01:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com