Thanks in advance for any help.

Let me first describe my table: I have a table in Excel that is tied to data in Access. There is a button that I can press that will automatically import the data from Access based on a date and set of times inputted by the user. If the data pulled fills in 15 rows, then the table expands or shrinks to 15 rows. If it's 30, then it grows to 30. The user then can add to the data and export it back to Access.

What I am working on doing is adding an Update button. This will work similarly to the Import button, but will recognize any data that has been added to the Excel table and instead of overwriting that data, will simply update and add or remove any rows based on the new parameters.

My problem: To update and not replace current data in Excel, I am using the following code:

Sub updatetest()

Dim AccessID(), ID_Count, z As Integer
Dim Switched(), Reason(), Verbiage() As String
Dim rID, rReplace As Range

ActiveSheet.Unprotect

Set rID = Range("Import[Access ID]")

'Count the number of IDs
ID_Count = rID.Count

'ReDim to reflect how many units are the in the Access ID, Switched, Reason, and Verbiage arrays
ReDim AccessID(ID_Count), Switched(ID_Count), Reason(ID_Count), Verbiage(ID_Count)


'Assigns each value in the Access ID, Switched, Reason, and Verbiage arrays a variable
For z = 1 To ID_Count
    AccessID(z) = Range("B11").Offset(z, 0)
    Switched(z) = Range("P11").Offset(z, 0)
    Reason(z) = Range("Q11").Offset(z, 0)
    Verbiage(z) = Range("R11").Offset(z, 0)
Next

'<Insert UPDATE code here>

For z = 1 To ID_Count
    On Error Resume Next
    Set rReplace = rID.Find(What:=AccessID(z), LookAt:=(xlWhole))
    Range(rReplace.Address).Offset(0, 14).Value = Switched(z)
    Range(rReplace.Address).Offset(0, 15).Value = Reason(z)
    Range(rReplace.Address).Offset(0, 16).Value = Verbiage(z)
Next

'ActiveSheet.Paste

Range("B12").Select

ActiveSheet.Protect

End Sub
If the updated table is the exact same number of rows, it works fine. If its fewer rows, it works fine. The problem is if the table expands. What will occur is that it will only search the same number of rows that were in the table initially. So, if I have 15 rows and update it to make 30, it will only search the first 15 rows on the new table and the rest of the data I am looking to replace gets lost.

Sorry about any confusion in my explanation, but I am happy to clarify any details you may need to offer assistance.

Thanks again!