VBA Excel - Remove a row from a multiple sheet if it is equal to

Question

I am currently working on deleting rows. I have already made it work in one sheet, but I just want to ask if there is any way to delete rows in several sheets at the same time? I have a unique key which is the student ID that is in Column C of all the sheets that will be affected. So, by clicking on the delete button, all data with this student ID will be deleted.

Using the code below, I can delete a row from the STUDENTS_INFO sheet.

Sub del_stud()

    Set ws = ActiveWorkbook.Worksheets("STUDENTS_INFO")
    LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    For r = 10 To LastRow
        If CStr(ThisWorkbook.Sheets("HOME").Range("K11").Value) = ws.Cells(r, 3) Then
            ws.Rows(r).EntireRow.Delete
            MsgBox "Student's data is now deleted!"
            Unload Me
        End If
    Next r
    
End Sub

The sheets that will be affected are STUDENTS_INFO, G1-Q1, G1-Q2, G1-Q3, G1-Q4, G2-Q1, G2-Q2, G3-Q3, G4-Q4, and so on... I also have sheets that, hopefully, will not be touched. Is this possible?

Based on my research, it uses the For Each ws In ThisWorkbook.Sheets. I tried to use it, but it still deletes the row in STUDENTS_INFO sheet and not on multiple sheets.

Here's the code that I tried.

Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Sheets
        LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        For r = 10 To LastRow
            If CStr(ThisWorkbook.Sheets("HOME").Range("K11").Value) = ws.Cells(r, 3) Then
                ws.Rows(r).EntireRow.Delete
                MsgBox "Student's data is now deleted!"
                Unload Me
            End If
        Next r
    Next ws
    
Application.ScreenUpdating = True

Note, when looping and deleting it's better to use `Union`, as demonstrated [here](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). Another option is to loop from the bottom up. — BigBen, Jan 29 '21 at 03:28
"I also have sheets that, hopefully, will not be touched. Is this possible?" Yes, by checking the worksheet's `.Name`. — BigBen, Jan 29 '21 at 03:29
I would just filter each sheet for your target ID and then delete the visible cells. No need to loop here — urdearboy, Jan 29 '21 at 04:07
Also, @BigBen meant as demonstrated [here](https://stackoverflow.com/a/52637747/6706419) ;) — urdearboy, Jan 29 '21 at 04:08
Not sure why your code is working on one sheet but not any others. Is there any other relevant code not shown? Like maybe `On Error` or something? — urdearboy, Jan 29 '21 at 04:10
@urdearboy What do you mean by this `Not sure why your code is working on one sheet but not any others.`? Are my codes correct? — cjvdg, Jan 29 '21 at 04:58
So, is there a single row (per sheet) to be deleted? If yes, you maybe have different cells format in the other sheet. In such a case, please try changing of `ws.Cells(r, 3)` with `CStr(ws.Cells(r, 3).value)`. — FaneDuru, Jan 29 '21 at 08:27

score 1 · Accepted Answer · 2021-01-29T06:05:22.960

1

I’d agree with @urdearboy’s suggestion of using a filter to delete the rows – plus looping through an array of sheets that you designate. The following code assumes the Student ID is sourced from the cell K11 on the HOME sheet. You can add/remove sheets from the array as you see fit.

Try the following & let me know how you go.

Option Explicit
Sub del_stud()
Dim StudID As String, ws As Worksheet

'Get the filter criteria from cell K11 in the HOME sheet
StudID = ThisWorkbook.Sheets("HOME").Range("K11").Value

'Do the STUDENTS_INFO sheet by itself
With ThisWorkbook.Sheets("STUDENTS_INFO").Cells(8, 3).CurrentRegion
    .AutoFilter 1, StudID
    .Offset(1).EntireRow.Delete
    .AutoFilter
End With

'Do the other generic sheets next - add/remove sheets as required
For Each ws In Sheets(Array("G1-Q1", "G1-Q2"))

    With ws.Cells(9, 3).CurrentRegion
        .AutoFilter 1, StudID
        .Offset(1).EntireRow.Delete
        ws.AutoFilterMode = False
    End With

Next ws

End Sub

edited Jan 29 '21 at 06:05

answered Jan 29 '21 at 05:10

I'm getting an error that says `AutoFilter method of Range class failed`. This is the highlighted line `.AutoFilter 1, StudID` – cjvdg Jan 29 '21 at 05:14
Some buttons and other stuff. The header of the table is on `Row 8`, so that data starts at `row 9` then `column C` – cjvdg Jan 29 '21 at 05:21
Yes. All the `G1-Q1` and so on are all the same except for the `STUDENTS_INFO` but the rows and columns of the `StudID` are the same in each sheet. The result of the revision is that it deletes the header of `G1-Q1` and `Q2-G1` and not the `StudID`. – cjvdg Jan 29 '21 at 05:41
Can I ask if what's the `1` in `.AutoFilter 1, StudID`? – cjvdg Jan 29 '21 at 05:51
Headers for `STUDENTS_INFO` are on `Row 8`. – cjvdg Jan 29 '21 at 05:52
It is not accurate. Sometimes it deletes, sometimes it's not. Not all sheets with the `Stud ID` is deleted. – cjvdg Jan 29 '21 at 06:45
Is the `9` in `With ws.Cells(9, 3).CurrentRegion` is the start of the data or it is for the header? And is the `1` in the `.Offset(1).EntireRow.Delete` is the value when the filter started? – cjvdg Jan 29 '21 at 07:06
Unfortunately, I’m unable to replicate the errors you’re getting at my end. There’s no reason why the code should work on some sheets and not others if all sheets are included in the array, and the identical student ID appears on all sheets of interest, Sorry I can’t help you any further, but hopefully I’ve given you some ideas on how to proceed, particularly with how to loop through an array of sheets. – Jan 29 '21 at 20:11
With regard to your last comment, the `9` (and the `8`) refers to the header row, and the `1` in the `.Offset(1)` offsets the range by 1 row to exclude the header row from being deleted along with the Student ID row. – Jan 29 '21 at 20:11
Hello! I made it work! I just removed the `.CurrentRegion` to your codes and correct the numbers since my worksheets have different columns and rows. Thanks for your explanation. It helps a lot! – cjvdg Feb 01 '21 at 00:09
Good to hear - and always extra satisfaction when we work it out for ourselves. Well done! – Feb 01 '21 at 00:52
Update: No need to remove the `CurrentRegion`, just make sure that there is no blank cell for it to work. – cjvdg Feb 01 '21 at 06:20

VBA Excel - Remove a row from a multiple sheet if it is equal to

1 Answers1

Linked