Delete table rows if time in column is 07 45

0 votes

Although I'm new to VBA and have much of what I need working, I've run into trouble with this final step.

The sheet is being used to paste data into, and from the pasted data, a table is being created for a particular purpose.

I need to create a macro that will eliminate all the rows in a table whose "Time" column contains a time that is later than 07:45.

The table is as follows:

ID Surname Init Location Time Event Destination
12 Name1 I1 Loc1 18:00 Ev1 Dest1
34 Name2 I2 Loc2 07:45 Ev2 Dest2
56 Name3 I3 Loc3 11:00 Ev3 Dest3
78 Name4 I4 Loc4 05:00 Ev4 Dest4

This is a screenshot of the table currently

After running the macro, it should look like this:

ID Surname Init Location Time Event Destination
34 Name2 I2 Loc2 07:45 Ev2 Dest2
78 Name4 I4 Loc4 05:00 Ev4 Dest4

This is how the table should look after

I've already used this code to delete empty table rows on a different file, but I'm not sure how to adapt it to work for this purpose:

    Dim EventsRng As Range
    On Error Resume Next
    Set EventsRng = Range("Events[[ID]]").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not EventsRng Is Nothing Then
        EventsRng.Delete Shift:=xlUp
    End If

I was also suggested this, but haven't been able to adapt it to work either:

Private Sub deleteTableRowsBasedOnCriteria(tbl As ListObject, columnName As String, criteria As String)

    Dim x As Long, lastrow As Long, lr As ListRow
    lastrow = tbl.ListRows.Count
    For x = lastrow To 1 Step -1
        Set lr = tbl.ListRows(x)
        If Intersect(lr.Range, tbl.ListColumns(columnName).Range).Value = criteria Then
            'lr.Range.Select
            lr.Delete
        End If
    Next x

End Sub
Dim tbl As ListObject
Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Events")
Call deleteTableRowsBasedOnCriteria(tbl, "Time", ">07:45")
Mar 20, 2023 in Others by Kithuzzz
• 38,000 points
383 views

1 answer to this question.

0 votes

add a formula in a hidden column that looks at if the time in the Time column is >07:45 and if it is, it puts "DEL" in the hidden column.

=IF(E4>”07:45”,”DEL”,” “)

I then use the second macro in my original question to delete any table rows in the hidden column that contain “DEL”.

Finally, I use the first macro in my original question to delete any remaining empty cells.

answered Mar 20, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Copy last 3 rows, excluding the rows for which there is a "0" in column "C"

The copy inside the loop is overwriting ...READ MORE

answered Jan 10, 2023 in Others by narikkadan
• 63,600 points
475 views
0 votes
1 answer

Is there a faster way to delete table rows so my script doesn't take hours to run?

This is for your rowAddressToRemove variable. So instead of ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,600 points
828 views
0 votes
1 answer

Check if cell found is in column "AP"

Find the Last Cell in the Row ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,000 points
797 views
+1 vote
1 answer

Between cyber security and CCNA profession which one is best in terms of time to become an expert and salary payment

CCNA professional is more inclined towards the ...READ MORE

answered Dec 18, 2019 in Others by Pri
2,058 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,244 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,689 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
949 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,082 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
2,073 views
0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,600 points
886 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP