Thread: Removing Rows based upon Criteria in Cells

I have a worksheet where I need to look for several employees names that are "exempt" and I need to delete those rows out and add them to a different sheet.
I was wondering if it is possible to have a macro look at the names in column A in worksheet "Exempt List" (which the list can contain as few as 1 or as many as 150 employees), and cut the rows that have those names in the "ReportExcel" column G. The same names will be listed several times, typically once per week and then Paste those Rows in the "Exempt" worksheet.

Any thoughts would be appreciated,

Hi
not sure whether you identify exempt people from column A or Column G. This macro assumes column A - change to G if that's where the names are. it also assumes a column heading in row 1, so doesn't move text from that row

Sub copy_names()
Dim ToRow, FromRow
FromRow = Sheets("Exempt List").UsedRange.Cells(Sheets("Exempt List").UsedRange.Cells.Count).Row
ToRow = Sheets("Exempt").UsedRange.Cells(Sheets("Exempt").UsedRange.Cells.Count).Row + 1

Sheets("Exempt List").Select
Columns("A").Cells(FromRow).Select
Do While FromRow > 1 'leaves column heading in first row
    If Sheets("Exempt List").Rows(FromRow).Columns("A").Value <> "" Then
        Sheets("Exempt List").Rows(FromRow).Columns("A").EntireRow.copy Destination:=Sheets("Exempt").Rows(ToRow).Cells(1)
        Sheets("Exempt List").Rows(FromRow).EntireRow.Delete
       ToRow = ToRow + 1
    End If
    FromRow = FromRow - 1
Loop
End Sub

It appears this is just cutting the names from the Exempt list and transferring it to the Exempt worksheet, what needs to happen is the entire rows that match a name in column A in "Exempt" in column G in "ReportExcel"...that row in the worksheet "ReportExcel" needs to be cut out and transferred to the "Exempt" worksheet. the "Example" worksheet shows what should happen.

yes that is what I mean, its kind of hard to say...
there is a file in the original thread, with an example of all the data...
also it is important that the integrity (or formatting) of the "reportexcel" worksheet should look as if those rows never existed...
I appreciate your assistance

The data needs entered starting in cell A2 of the "Exempt" worksheet as there will be a header row just like in the "ReportExcel" worksheet. I have attached another file in a different format for users with 2007 or 2010 version. Any guidance would be appreciated.
The file is below
Exempt.xlsx

So the following code finds the items located in the column A of "Exempt List" worksheet in column G of the "ReportExcel" and copies the entire row to the "Exempt" worksheet, but the issue is it doesn't cut the info from the "ReportExcel" worksheet.

Sub Exempt()
    
    Dim ka, k(), a, i As Long, c As Long, n As Long, dic  As Object
    
    '// user settings
    Const strSourceData         As String = "ReportExcel"   'Sheet Name
    Const strNumsToKeep         As String = "Exempt List"   'Sheet Name
    Const strDataColumns        As String = "a:g"
    Const strNumbersRange       As String = "a2:a400"
    Const lngNumColInData       As Long = 7
    Const strDestRange          As String = "a2"
    '// end of settings
    
    
    With Worksheets(CStr(strSourceData))
        ka = Intersect(.UsedRange, .Columns(CStr(strDataColumns)))
    End With
    
    With Worksheets(CStr(strNumsToKeep))
        a = Intersect(.UsedRange, .Range(CStr(strNumbersRange)))
    End With
    
    Set dic = CreateObject("scripting.dictionary")
        dic.comparemode = 1
        
    For i = 1 To UBound(a, 1): dic.Item(CStr(a(i, 1))) = Empty: Next
    
    ReDim k(1 To UBound(ka, 1), 1 To UBound(ka, 2))
    
    For i = 1 To UBound(ka, 1)
        If dic.exists(CStr(ka(i, lngNumColInData))) Then
            n = n + 1
            For c = 1 To UBound(ka, 2): k(n, c) = ka(i, c): Next
        End If
    Next
    
    If n Then Worksheets("Exempt").Range(CStr(strDestRange)).Resize(n, UBound(ka, 2)).Value2 = k
    
End Sub

Any idea of what code I can enter to cut the data needed from the "ReportExcel" worksheet?

Any thoughts if I can tweak this code to cut instead of copy?

after reading hundreds of similiar threads, I found this code

Sub Test()

  Dim rngChk As Range
  Dim rngVis As Range
  Dim rngDel As Range
  Dim wa As Worksheet

  With ActiveSheet
    If .FilterMode Then .ShowAllData
    With .UsedRange
      If .Cells.Count < 2 Then Exit Sub
      Set rngChk = Cells(.Row, .Column + .Columns.Count).Resize(.Rows.Count)
    End With
  End With
  
  With rngChk
    .Formula = "=SUMIF(A:A,A" & .Row & ",L:L)"
    .AutoFilter 1, 0
    On Error Resume Next
    Set rngVis = .Offset(, 1).SpecialCells(xlCellTypeVisible)
    Set rngDel = .Rows.Resize(.Rows.Count - 1).Offset(1, 1).SpecialCells(xlCellTypeVisible).EntireRow
    .AutoFilter
    .EntireColumn.Delete
  End With
  If Err Then Exit Sub

  Set wa = Worksheets("RAD Report")
  On Error GoTo 0
  If wa Is Nothing Then
    Set wa = Sheets.Add(After:=Sheets(Sheets.Count))
    wa.Name = "RAD Report"
  Else
    wa.UsedRange.Clear
  End If
  rngVis.EntireRow.Copy wa.Range("A1")
  wa.UsedRange.EntireColumn.AutoFit
  rngVis.Parent.Activate
  rngDel.Delete

End Sub

So I know this code will not work in its current state to my worksheet, but can it be altered for my needs?

That works brilliantly for my example file, but in my real file I have data in columns A-S, how can I change this code to cut and paste the data from all the columns? I have changed the parameters every way imaginable, and nothing seems to be working for me.?.

Thank you, I really appreciate your assistance!