Going over your code, let's start with one potential issue. Your code will aways work with up to 500.000 rows.
If you have more rows, then 500.000 rows, then you will miss rows 500.001 and beyond.
If you fewer rows, then your formula might be needlessly slow. (though to be fair, I don't think the speed will make much of a difference)
VBA Code:
If Not Application.Intersect(Target, Range("O2:O500000")) Is Nothing Then
I would change that, so the macro identifies the last row and uses that as a variable inside the macro.
VBA Code:
LastRowData = Sheets(ENTERSHEETNAMEHERE).Cells(sheets(ENTERSHEETNAMEHERE).Rows.Count, "A").End(xlUp).Row
If Not Application.Intersect(Target, Range("O2:O" & LastRowData)) Is Nothing Then
Going through the macro, it looks like you have the drop-down list in column O.
The macro is written so that whenever something changes in the entire worksheet, the macro will run.
The code will first check if there is something in column O of the active row.
If there is not, the macro will skip all next steps and close.
If there is something in there, the macro will continue.
VBA Code:
If Not Application.Intersect(Target, Range("O2:O500000")) Is Nothing Then
This code will check if the entry in the column O says "Archive" (that's the Target.value).
If it is not archive, the macro will skip all next steps and close.
If it does say "Archive", then the macro will take the active row and places it in the variables list as "
fromRow".
It also places your worksheet called "Archive" and places it in the variables list as "
Archivelist"
VBA Code:
Set archiveList = ThisWorkbook.Worksheets("Archive")
If Target.Value = "Archive" Then
fromRow = ActiveCell.Row
Having basically "copied" your desired row into memory, it then continues.
It goes to the Archivelist, and check if it has been filtered.
VBA Code:
With archiveList
If .FilterMode Then
If it has been filtered, it will count how many cells in column A:A are not empty. , and thus identifies the first EMPTY row.
This empty row is copied into the variables list as "
ArchiveRow".
I can't really go into detail explaining
how it does what it does. I am not exactly sure how it does what it does, but I do know it counts the number of rows that are not empty.
VBA Code:
Dim strMatch As String
strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
archiveRow = Evaluate(strMatch) + 1
If there is no filter, then the last visible row is taken.
This empty row is copied into the variables list as "
ArchiveRow".
To be honest, I am not quite sure what the 3 refers to in End(3).
VBA Code:
Else
archiveRow = archiveList.Cells(archiveList.Rows.Count, 1).End(3).Row + 1
End If
End With
Having decided where the changed row has to go, it now copies the changed row into the archive.
It takes the contents of the change row (FROMROW) from column 1 through column 15, and copies it into the first emtpy row of the archive. That's the "
ArchiveRow" identified earlier.
VBA Code:
Range(Cells(fromRow, 1), Cells(fromRow, 15)).Copy archiveList.Cells(archiveRow, 1)
And it closes everything of by deleting the row that was changed:
VBA Code:
Rows(fromRow).EntireRow.Delete
