How to use comma separated inputs in macro

[Pawan]

Hello

I have strange requirement.
I have a column with hundreds of entries in it. There is one other columns
associated eith it. For an example consider that following are the entries in
column A and B

Column A Column B

PI03590 bar
ZT05693 %
PIC098734 N
TT09823 C

Now I have to provide one userform input box to user. The user will enter
entries separated by comma (or any other character). I have to filter entres
in column B as per these inputs.
e.g. If user enters (bar, N) in the input box, then macro should filter
entries in column B and copy "unmatched" result to new sheet. In this case,
Macro should copy following information in new sheet:


ZT05693 %
TT09823 C.

(I do not want the user to manually filter it in the sheet as this needs to
be incorporated in the already existing macro which performs several actions
on the sheet.)

Thank You in advance

 

[Rick Rothstein]

I think this macro will do what you want, just assign the appropriate sheet
names to the two Const (constant) statements (and rename the macro to
something more appropriate)...

Sub CopyNonSpecifiedColumnBItemRows()
Dim UserInput As String, UserList() As String, Cell As Range
Dim SourceLastRow As Long, DestinationLastRow As Long
Const Source As String = "Sheet4"
Const Destination As String = "NewSheet"
SourceLastRow = Worksheets(Source).Cells(Rows.Count, "B").End(xlUp).Row
DestinationLastRow = Worksheets(Destination).Cells( _
Rows.Count, "B").End(xlUp).Row
UserInput = InputBox("List items separated by commas", "Get User List")
If Len(UserInput) Then
UserInput = "," & Replace(UserInput, " ", "") & ","
For Each Cell In Worksheets(Source).Range("B1:B" & SourceLastRow)
If InStr(1, UserInput, "," & Cell.Value & ",", vbTextCompare) = 0 Then
Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")
DestinationLastRow = DestinationLastRow + 1
End If
Next
End If
End Sub

 

[Pawan]

Thanks Rick.

However I am getting error at the line

Worksheets(Destination).Cells(DestinationLastRow, "A")

 

[Rick Rothstein]

It looks like the newsreader word-wrapped that line from the line above it.
What you are seeing as this...

Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")

should all be on one line with a space character between them, like this...

Cell.EntireRow.Copy Worksheets(Destination).Cells(DestinationLastRow, "A")

 

[Pawan]

Thanks Rick. Its perfect.

It looks like the newsreader word-wrapped that line from the line above it.
What you are seeing as this...

Cell.EntireRow.Copy
Worksheets(Destination).Cells(DestinationLastRow, "A")

should all be on one line with a space character between them, like this...

Cell.EntireRow.Copy Worksheets(Destination).Cells(DestinationLastRow, "A")

--
Rick (MVP - Excel)




.