(Solved) – Shift the row values up in excel using macro after a row is deleted


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

    End If

Next c


End Sub

I am not deleting the entire row because There are values in further columns which I don’t want to touch

After the code is run, my database looks like this:
enter image description here

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!

Leave a Reply

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