Moving rows to another sheet based on several keywords


Hello, I am working with a database of User accounts that include human and non human users. I am trying to separate a select set of users into another sheet as they will be processed differently than the Users I need to work with.
I have the below code, which works fine, I am just trying to make this more efficient by not having to use OR or have multiple For/Next for each Keyword.


In my list of users, there are many that are "Vacant", "Spare", "Available", "Unused", etc.


Is there a way to add another For/Next loop into the value filed so to not have to add the OR or do separate For/Next Loops within my Function? The Keywords can be pulled from a pre determined list from another set of cells if need be, I just cant seem to make it work.

Here is my snippet currently, which works, just trying to streamline. I omitted irrelevant code for this question.


Any help would be greatly appreaciated! I can add the entire function if needed.

For K = 1 To xRg2.Count
If InStr(xRg2(K).Value, "Spare") Or InStr(xRg2(K).Value, "Vacant") > 0 Then
xRg2(K).EntireRow.Copy Destination:=Worksheets("Vacant-Spare").Range("A" & J + 1)
If CStr(xRg2(K).Value) = "Spare" Or CStr(xRg2(K).Value) = "Vacant" Then
K = K - 1
End If
J = J + 1
End If

1 Reply



Depending on what version of Excel you're working with, it might well be possible to do what you're seeking to do without a VBA routine. The most recent version has a number of Dynamic Array functions that can FILTER (that's one of them) a data table according to criteria and give you just the rows that meet your criteria.


One of us here could demonstrate that, but we'd need to work with a representative sample of your actual worksheet---just remove any real names and other identifiable data.