Search code examples
excelvbasetrowsdelete-row

A way to delete rows if row number is contained in a set?

Currently I am using this code to delete rows from the bottom up:

Sub Deleterows()

With Sheet8


.Rows(9).EntireRow.Delete
.Rows(5).EntireRow.Delete
.Rows(1).EntireRow.Delete

End With

End Sub

I have quite a lot of rows to delete. It will always be the same rows that need deleting.

I want to know if there is a better way to delete my 50 specific rows without writing .Rows(x).EntireRow.Delete 50 times.

For example if row number in [9,5,1] then delete. This would also be easier to just add a row number into without repeating a line.

This is a few lines from the data extract. Note it has been transposed.

enter image description here

Solution

  • If you need to all time the same rows, please use the next approach. It does not use any iteration and will delete them at once. You can build the array in any order:

     Dim sh As Worksheet, delRange As Range, arr As Variant
 
 arr = Array(1, 7, 5, 3, 19) 'build here the rows to be deleted array
 Set sh = ActiveSheet
 Set delRange = sh.Range("A" & Join(arr, ",A"))
 delRange.EntireRow.Delete xlUp