So, I am using this macro to search by id(serial number) and then the delete the row elements for that particular row.
The code for deleting the row is:
Sub DeleteMe() 'declare the variables Dim ID As Range, c As Range, orange As Range Dim wb As Workbook Set wb = ThisWorkbook Set Fws = wb.Sheets("Data") Set Bws = wb.Sheets("Bookings") Dim lastrow As Long 'set the object variable Set ID = Bws.Range("B3") 'stop screen flicker Application.ScreenUpdating = False lastrow = Fws.Range("A" & Rows.Count).End(xlUp).Row Set orange = Fws.Range("A2:A" & lastrow) 'find the value in the range For Each c In orange If c.Value = ID.Value Then 'delete the row c.Cells(1, 2).Clear c.Cells(1, 3).Clear c.Cells(1, 4).Clear c.Cells(1, 5).Clear c.Cells(1, 6).Clear c.Cells(1, 7).Clear c.Cells(1, 8).Clear c.Cells(1, 9).Clear c.Cells(1, 10).Clear c.Cells(1, 11).Clear c.Cells(1, 12).Clear c.Cells(1, 13).Clear c.Clear End If Next c Sheet2.Select
I am not deleting the entire row because There are values in further columns which I don’t want to touch
What I want is to shift all the remaining rows up to fill the empty row. I also want the serial numbers to change accordingly. So in this case 7 will become 6 and 8 will become 7.
Thanks in advance!