CODEWITHSUNDEEP
excelvbaexcel-formula
Omega
Omega

Reputation: 185

Delete all rows after N number in excel

I have a excel having 100 of sheets. Each sheet is having more than 40K rows. I want to keep just first 20 rows(or even any 20 rows is fine if first 20 is not possible) and delete all the remaining rows. How to do this in excel? Is there any formula available. Currently I'm manually selecting and deleting the rows.

Upvotes: 0

Views: 506

Answers (2)

JvdV
JvdV

Reputation: 75840

You need a simple loop through your Worksheets, and as per your requirement; you can exclude certain sheets by putting them into an array. For example:

Sub Test()

Dim arr As Variant: arr = Array("Sheet1", "Sheet2") 'Exclude these
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    'Check if ws should be excluded
    With Application
        If .Count(.Match(ws.Name, arr, 0)) = 0 Then
            'Remove data from row 21 onwards
            ws.Range("21:" & ws.Rows.Count).EntireRow.Delete
        End If
    End With
Next

End Sub

Upvotes: 2

Odatas
Odatas

Reputation: 178

Sub WorksheetLoop()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("21:40000").EntireRow.delete
Next ws

End Sub

This should do the trick.

Upvotes: 2

Related Questions