Macro to delete every n+1 row across all worksheets

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
Please the number of first rows to be deleted INCREASES along with the worksheet numbers. For example, row 1 (first row) would be deleted in sheet 1, row 1 and row 2 would be deleted in subsequent sheet 2, row 1 row 2 row 3 would be deleted in subsequent sheet 3, and so on. Please any help on a macro that can process all the worksheets together as described?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this:
VBA Code:
Sub Delete_Rows()
'Modified 10/18/2021  11:22:29 PM  EDT
Application.ScreenUpdating = False
Dim i As Long

    For i = 1 To Sheets.Count
        With Sheets(i)
            .Rows(1).Resize(i).Delete
        End With
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
VBA Code:
 Sub Delete_Rows() 'Modified 10/18/2021 11:22:29 PM EDT Application.ScreenUpdating = False Dim i As Long For i = 1 To Sheets.Count With Sheets(i) .Rows(1).Resize(i).Delete End With Next Application.ScreenUpdating = True End Sub

Thank you it worked perfectly. But I made mistake when analyzing the question and would be glad if you could help me out. The process of first row deletion is to start from Sheeet 2. The Sheet 1 is to remain untouched. For example, row 1 (first row) would be deleted in sheet 2, row 1 and row 2 would be deleted in subsequent sheet 3, row 1 row 2 row 3 would be deleted in subsequent sheet 4, and so on. Note: Sheet 1 is not involved at all.
 
Upvote 0
Change this line of code:
For i = 1 To Sheets.Count

To:
For i = 2 To Sheets.Count
 
Upvote 0
Doing as you explained above, although Sheet 1 remained untouched, but Sheet 2 (where we want the process to start from) instead deleted first two rows, then subsequent Sheets deletes every continuous first row in their own Sheets as normal. That is, row 1 and row 2 were deleted in sheet 2 (instead of deleting just and only row 1), row 1 and row 2 were deleted in subsequent sheet 3, row 1 row 2 row 3 were deleted in subsequent sheet 4, and so on

However, I went further to adjust the code like below

For i = 1 To Sheets.Count
With Sheets(i+1)
.Rows(1).Resize(i).Delete
End With

The "Sheets(i+1)" did the work as expected, but at end of run it displays a dialog box warning as below

Run-time error '9'
Subscript out of range
 
Upvote 0
Try this:
VBA Code:
Sub Delete_Rows()
'Modified  10/19/2021  2:54:31 PM  EDT
Application.ScreenUpdating = False
Dim i As Long

    For i = 2 To Sheets.Count
        With Sheets(i)
            .Rows(1).Resize(i - 1).Delete
        End With
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Your original Post Subject title said:

Macro to delete every n+1 row across all worksheets​

Never said start on sheet 2
 
Upvote 0
It worked perfectly. Thank you very very much. I can't thank you enough.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top