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

user_6729785 0
Viewed 198

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
vba excel
1 Answers

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
Admin1
Related