Hello,

I have a worksheet 'SBL' with some data.
Headers in first row i.e. from A1 to K1.

In columnJ sarting from J2, I have some user names.
I have a defined name TUsers for all the user names in worksheet
Temp.

I want to remove all other rows in worksheet 'SBL', leaving behind
rows of TUsers only.


Any bright ideas?

if tusers is a single column or single row, you could add a helper column:

=ismatch(j2,tusers,0)
and copy down this helper column

It'll return True or false.

Then you could apply data|Filter|autofilter (xl2003 menus) to show just the rows
you want to delete.

Then delete those visible rows.

You may want to keep the rows, but just hide them with the autofilter????

Rambo wrote:

Hello,

I have a worksheet 'SBL' with some data.
Headers in first row i.e. from A1 to K1.

In columnJ sarting from J2, I have some user names.
I have a defined name TUsers for all the user names in worksheet
Temp.

I want to remove all other rows in worksheet 'SBL', leaving behind
rows of TUsers only.

Any bright ideas?

--

Dave Peterson

Typo alert...

Use this instead.

=isnumber(match(j2,tusers,0))

Dave Peterson wrote:

if tusers is a single column or single row, you could add a helper column:

=ismatch(j2,tusers,0)
and copy down this helper column

It'll return True or false.

Then you could apply data|Filter|autofilter (xl2003 menus) to show just the rows
you want to delete.

Then delete those visible rows.

You may want to keep the rows, but just hide them with the autofilter????

Rambo wrote:

Hello,

I have a worksheet 'SBL' with some data.
Headers in first row i.e. from A1 to K1.

In columnJ sarting from J2, I have some user names.
I have a defined name TUsers for all the user names in worksheet
Temp.

I want to remove all other rows in worksheet 'SBL', leaving behind
rows of TUsers only.

Any bright ideas?

--

Dave Peterson

--

Dave Peterson

This may get you started:

Sub Filter()
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim myArray As Variant

myArray = ThisWorkbook.Names("TUsers").RefersToRange.Value

FirstRow = 2
LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsError(Application.Match(Cells(iRow, "J").Value, myArray, 0))
Then
Rows(iRow).Delete
Else
'do nothing
End If
Next iRow
End Sub

Run this with SBL the active sheet. It puts your named range of users into
an array. It then loops upward in SBL column K matching the cell against the
array. If it does not match the array that row is deleted, else if a match
the row is kept.

Mike F
"Rambo" wrote in message
...
Hello,

I have a worksheet 'SBL' with some data.
Headers in first row i.e. from A1 to K1.

In columnJ sarting from J2, I have some user names.
I have a defined name TUsers for all the user names in worksheet
Temp.

I want to remove all other rows in worksheet 'SBL', leaving behind
rows of TUsers only.


Any bright ideas?