(Solved) – Deleting Unused Table Rows

  • by

I have a sheet with 6 tables with each table using columns B:N. Column B contains hours from 1AM to 12AM per table. I need to delete all rows below the cells that contain a specific value on cell AF2. For example, AF2 contains 5PM. All rows below 5PM on column B on each table should be deleted. All tables have titles like first table is Cashiers, second table is Waiters, and so on and so forth.

This is what I have so far:

Set sh = Sheets("report")
valueToFind = sh.Range("AF2").Value

Set Cell1 = sh.Range("B:B").Find(What:=valueToFind, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Cell1 Is Nothing Then Exit Do
Set Cell2 = sh.Range(Cell1.Address & ":B" & sh.UsedRange.Rows.Count).End(xlDown)
    If IsEmpty(Cell1.Offset(1, 0)) Then
        Exit Sub
        Rows(Cell1.Row & ":" & Cell2.Row).Delete
    End If

The problem with this code is that it also deletes the row with the time that’s indicated on cell AF2, when it should move one cell down then delete starting that row down.

Any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *