VBA Excel-如果它等于 [英] VBA Excel - Remove a row from a multiple sheet if it is equal to

问题描述

我目前正在删除行.我已经使它可以在一张纸上工作，但是我只想问一下是否有任何一种方法可以同时删除几张纸上的行?我有一个唯一的键，它是所有受影响的工作表的 C列中的 Student ID .因此，通过单击删除按钮，具有此学生ID 的所有数据都将被删除.

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.

使用下面的代码，我可以从 STUDENTS_INFO 工作表中删除一行.

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

将受影响的工作表为 STUDENTS_INFO ， G1-Q1 ， G1-Q2 ， G1-Q3 ， G1-Q4 ， G2-Q1 ， G2-Q2 ， G3-Q3 ， G4-Q4 ，依此类推...等等，我也希望一些表格不会被触及.这可能吗?

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?

根据我的研究，它使用 ThisWorkbook.Sheets中的每一个ws .我尝试使用它，但它仍会删除 STUDENTS_INFO 工作表中的行，而不是多个工作表中的行.

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.

这是我尝试过的代码.

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

推荐答案

我同意@urdearboy的建议，即使用过滤器删除行-并循环遍历您指定的工作表数组.下面的代码假定学生ID来自HOME表格上的单元格K11.您可以根据需要从数组中添加/删除工作表.

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

这篇关于VBA Excel-如果它等于的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！