0

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dasal Kalubowila
  • 103
  • 5
  • 22
  • 2
    Can't you find a criteria in the rows to be deleted to also avoid writing their numbers? You will gain very little proceeding in this way... Do you all the time need to delete **the same row numbers**? – FaneDuru Sep 07 '20 at 13:06
  • 1
    If you record a macro you will notice the code is `Range("9:9,5:5,1:1").Delete`. BTW I agree with Fane here. If there is a criteria then maybe using an Autofitler to identify the rows and then deleting them? – Siddharth Rout Sep 07 '20 at 13:09
  • This post may give some ideas [Delete rows...](https://stackoverflow.com/questions/59750930/delete-rows-using-range-find-method/59751060#59751060) – T.M. Sep 07 '20 at 13:12
  • Here are two links which can help you achieve what you want. [Using Autofilter](https://stackoverflow.com/questions/11317172/delete-row-based-on-partial-text/11317372#11317372) and [Using Union](https://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows/9379968#9379968) – Siddharth Rout Sep 07 '20 at 13:15
  • The data has been transposed so all the old column headers are now in Column A and the data is in columns (B:AE). I could apply a filter on column A to and tick/untick my fields, but it's no different to deleting specific rows? – Dasal Kalubowila Sep 07 '20 at 13:57
  • If you really want speed AND the format is not a problem, you can put the whole usedrange in an array in VBA, set all sheet to "", delete lines in the array and then put it all in the sheet. – Pierre Sep 08 '20 at 07:29

2 Answers2

4

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
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Perfect. Thanks Fane. Solves my problem perfectly. – Dasal Kalubowila Sep 07 '20 at 14:01
  • @Dasal Kalubowila: Glad I could help! But I still cannot understand why you need to manually build such an array instead finding some criteria, based on what to set the range... – FaneDuru Sep 07 '20 at 14:04
  • Can you see my above comment in regards to the data already being transposed? Column A has all the headers and columns B:AE has the data. Are you saying apply a filter and untick/tick the headers I want to delete/keep in Column A? Will that not be just as manual? – Dasal Kalubowila Sep 07 '20 at 14:15
  • @Dasal Kalubowila: I could see it only now. But, even in such a situation, I did not wont to suggest a filtering method. If there is a common criteria to identify these rows, a similar `delRange` can be built by using many method. If you like challenges, try me defining this criteria/algorithm and I will provide a solution to automatically build the range to be deleted. You can call the Sub/Function from inside the one making transposition... Now I must leave the office, but I can look at it when I will be at home. :) – FaneDuru Sep 07 '20 at 14:23
  • Thank you, but your solution below will suffice. – Dasal Kalubowila Sep 07 '20 at 14:33
  • 1
    Clever idea to build `delRange` via `Join(arr, ",A"))` +:) @FaneDuru – T.M. Sep 08 '20 at 06:59
2

As you mentioned, you can define a range of row's indexes to delete:

Dim rows2delete As Variant 
Dim i As Integer

rows2delete = Array(9, 5, 1) 'descending order is obligatory!

For i = LBound(rows2delete) To UBound(rows2delete) 
    Sheet8.Rows(rows2delete(i)).EntireRow.Delete
Next

Good luck!

For further details, please see: VBA - Array function

Maciej Los
  • 8,468
  • 1
  • 20
  • 35