Moving rows to another sheet based on several keywords

%3CLINGO-SUB%20id%3D%22lingo-sub-1471983%22%20slang%3D%22en-US%22%3EMoving%20rows%20to%20another%20sheet%20based%20on%20several%20keywords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1471983%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20working%20with%20a%20database%20of%20User%20accounts%20that%20include%20human%20and%20non%20human%20users.%20I%20am%20trying%20to%20separate%20a%20select%20set%20of%20users%20into%20another%20sheet%20as%20they%20will%20be%20processed%20differently%20than%20the%20Users%20I%20need%20to%20work%20with.%3CBR%20%2F%3EI%20have%20the%20below%20code%2C%20which%20works%20fine%2C%20I%20am%20just%20trying%20to%20make%20this%20more%20efficient%20by%20not%20having%20to%20use%20OR%20or%20have%20multiple%20For%2FNext%20for%20each%20Keyword.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20list%20of%20users%2C%20there%20are%20many%20that%20are%20%22Vacant%22%2C%20%22Spare%22%2C%20%22Available%22%2C%20%22Unused%22%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20add%20another%20For%2FNext%20loop%20into%20the%20value%20filed%20so%20to%20not%20have%20to%20add%20the%20OR%20or%20do%20separate%20For%2FNext%20Loops%20within%20my%20Function%3F%20The%20Keywords%20can%20be%20pulled%20from%20a%20pre%20determined%20list%20from%20another%20set%20of%20cells%20if%20need%20be%2C%20I%20just%20cant%20seem%20to%20make%20it%20work.%3C%2FP%3E%3CP%3EHere%20is%20my%20snippet%20currently%2C%20%3CSTRONG%3E%3CEM%3Ewhich%20works%3C%2FEM%3E%3C%2FSTRONG%3E%2C%20just%20trying%20to%20streamline.%20I%20omitted%20irrelevant%20code%20for%20this%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreaciated!%20I%20can%20add%20the%20entire%20function%20if%20needed.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20K%20%3D%201%20To%20xRg2.Count%3CBR%20%2F%3EIf%20InStr(xRg2(K).Value%2C%20%22Spare%22)%20Or%20InStr(xRg2(K).Value%2C%20%22Vacant%22)%20%26gt%3B%200%20Then%3CBR%20%2F%3ExRg2(K).EntireRow.Copy%20Destination%3A%3DWorksheets(%22Vacant-Spare%22).Range(%22A%22%20%26amp%3B%20J%20%2B%201)%3CBR%20%2F%3ExRg2(K).EntireRow.Delete%3CBR%20%2F%3EIf%20CStr(xRg2(K).Value)%20%3D%20%22Spare%22%20Or%20CStr(xRg2(K).Value)%20%3D%20%22Vacant%22%20Then%3CBR%20%2F%3EK%20%3D%20K%20-%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EJ%20%3D%20J%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1471983%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1472440%22%20slang%3D%22en-US%22%3ERe%3A%20Moving%20rows%20to%20another%20sheet%20based%20on%20several%20keywords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F702671%22%20target%3D%22_blank%22%3E%40AnthonySr78%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20what%20version%20of%20Excel%20you're%20working%20with%2C%20it%20might%20well%20be%20possible%20to%20do%20what%20you're%20seeking%20to%20do%20without%20a%20VBA%20routine.%20The%20most%20recent%20version%20has%20a%20number%20of%20Dynamic%20Array%20functions%20that%20can%20FILTER%20(that's%20one%20of%20them)%20a%20data%20table%20according%20to%20criteria%20and%20give%20you%20just%20the%20rows%20that%20meet%20your%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20us%20here%20could%20demonstrate%20that%2C%20but%20we'd%20need%20to%20work%20with%20a%20representative%20sample%20of%20your%20actual%20worksheet---just%20remove%20any%20real%20names%20and%20other%20identifiable%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

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)
xRg2(K).EntireRow.Delete
If CStr(xRg2(K).Value) = "Spare" Or CStr(xRg2(K).Value) = "Vacant" Then
K = K - 1
End If
J = J + 1
End If
Next

1 Reply
Highlighted

@AnthonySr78 

 

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.