-1

Can someone help me on removing specific rows in Excel and then shift cells up by using a macro? E.g. I need to delete rows 8, 14 and 79.

Thanks in advance for your help

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Sakis
  • 13
  • 5
  • Sorry i can't find something common about my question – Sakis Apr 02 '19 at 14:00
  • 2
    You could just push the record button on the developer ribbon and delete the rows to later check the code. Did you even do that? – Damian Apr 02 '19 at 14:02
  • wow i had no idea about that. Thanks Damian, i know you were ironical but you really helped me. Thanks again. – Sakis Apr 02 '19 at 14:06
  • Glad it helped. I wasn't being ironical, is the first thing you should try. – Damian Apr 02 '19 at 14:08
  • I didn't know about it's existence. This will help me a lot about my future issues. Thanks Damian. – Sakis Apr 02 '19 at 14:13
  • @Sakis After you used the Macro Recorder you will probably see a lot of `.Select` and `.Activate`. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to improve your recorded code to make it *much* faster, more realiable and a lot shorter. – Pᴇʜ Apr 02 '19 at 14:28
  • I did read one post about deleting rows that suggested working from the bottom up - logic being that if you delete row 8 then what was row 24 is now row 23 etc... – Solar Mike Apr 02 '19 at 14:44
  • @SolarMike Right you need to loop from bottom, or alternatively delete all rows at once (see answer below). – Pᴇʜ Apr 02 '19 at 15:05
  • @Pᴇʜ ok, so when all 3 are deleted at the same time it only re-numbers after that delete operation is complete : neat. thanks. – Solar Mike Apr 02 '19 at 15:07

1 Answers1

1

Try:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1") '<- Change sheet name if needed

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row '<- Find lastrow of column A where we assume data appears

        For i = LastRow To 1 Step -1 '<- Loop from bottom to top
            If i = 8 Or i = 14 Or i = 79 Then '<- If row is 8 or 14 or 79
                .Rows(i).EntireRow.Delete '<- Delete row
            End If

        Next i

    End With

End Sub

Another Solution suggested by Pᴇʜ (see comments):

Option Explicit

Sub test()

    ThisWorkbook.Sheets("Sheet1").Range("8:8,14:14,79:79").EntireRow.Delete

End Sub

Another Solution:

Option Explicit

Sub test()

    With ThisWorkbook.Sheets("Sheet1")
        Union(.Rows(8), .Rows(14), .Rows(79)).Delete Shift:=xlUp
    End With

End Sub
Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • This works perfectly for my purpose. Thank you so much for your help! I have no words to describe how much you've helped me with my project. – Sakis Apr 02 '19 at 14:11
  • 1
    If you are hardcoding the rows anyway... why not `ThisWorkbook.Sheets("Sheet1").Range("8,14,79").EntireRow.Delete`? You are taxing the execution time with a loop when there is no need to. – Damian Apr 02 '19 at 14:25
  • @Damian this will gives an error. if you are planning take any action in rows you should use .rows() instead of .range(). – Error 1004 Apr 02 '19 at 14:30
  • 1
    @Error1004 Use `ThisWorkbook.Sheets("Sheet1").Range("8:8,14:14,79:79").EntireRow.Delete` instead. – Pᴇʜ Apr 02 '19 at 14:31
  • `ThisWorkbook.Sheets("Sheet1").Range("A8,A14,A79").EntireRow.Delete` Anyway, my comment was about the 1 execution time vs the loop where you are hardcoding the rows. – Damian Apr 02 '19 at 14:32
  • @Error1004 Actually the critical point here is: You don't need to loop if you know the row number to delete! That's a waste of time. A loop is only needed eg. to check cell values for a criteria before delete. – Pᴇʜ Apr 02 '19 at 14:33
  • 1
    @Pᴇʜ i have edit my answer posting your answer too. – Error 1004 Apr 02 '19 at 14:34
  • Thanks so much for all your suggestions! I really learnt a lot today. – Sakis Apr 02 '19 at 14:49
  • @Error1004 I don't understand. I tasted it and worked great for 3 rows as your example. Now i've edited this including all rows i want to delete i take error "Wrong number of arguments or invalid property assignment". Can you please tell me why this happens? – Sakis Apr 02 '19 at 16:51
  • `Option Explicit Sub test() With ThisWorkbook.Sheets("Sheet1") Union(.Rows(8), .Rows(23), .Rows(25), .Rows(41), .Rows(65), .Rows(80), .Rows(82), .Rows(98), .Rows(122), .Rows(137), .Rows(139), .Rows(155), .Rows(179), .Rows(194), .Rows(196), .Rows(212), .Rows(236), .Rows(251), .Rows(253), .Rows(269), .Rows(293), .Rows(308), .Rows(310), .Rows(326), .Rows(350), .Rows(365), .Rows(367), .Rows(383), .Rows(407), .Rows(422), .Rows(424), .Rows(440), .Rows(464), .Rows(479), .Rows(481), .Rows(497)).Delete Shift:=xlUp End With End Sub ` – Sakis Apr 02 '19 at 16:51