Marie

Need more info. What range is "list"? What range is "BranchToDistrict" and
"DistrictNumber"?

If c.Offset(0, 1).Value = Range("DistrictNumber").Value
Then
Offset(0, 1).Value
Else: Selection.EntireRow.Delete

End If

If every row is being deleted, then this is probably always false. The THEN
part of this doesn't make any sense. If "List" comprises more than one
column, that's probably the problem.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thanks so much for looking at this!

the 'list' is the column of branch numbers I am downloading that I want to choose/delete from - the 'branchtodistrict' is a column that has all the possible branch numbers from all the districts(the range I named 'branchtodistrict' is only one column, in the next column I have the corresponding district numbers, but I didn't include this column in the named range, is that my mistake?) - the 'district number' is a cell I named that will always have the correct district number - the number for the file I am calculating data for. I want to look at the district number and pull from the 'list' only corresponding branch numbers, if it doesn't go with that district then delet it.

Am I all wet on my code? I'm new at this but really trying...

"Dick Kusleika" wrote:

Marie

Need more info. What range is "list"? What range is "BranchToDistrict" and
"DistrictNumber"?

If c.Offset(0, 1).Value = Range("DistrictNumber").Value
Then
Offset(0, 1).Value
Else: Selection.EntireRow.Delete

End If

If every row is being deleted, then this is probably always false. The THEN
part of this doesn't make any sense. If "List" comprises more than one
column, that's probably the problem.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Marie

Just so I understand: In the end, you want the range "list" to only contain
branches that are present in "branchtodistrict" and whose correspond
district is equal to "districtnumber". So you may have 20 branches in
"list", then after you run the macro you only have 5 branches in "list".

If that's right, try this

Sub FilterBranch()

Dim rDist As Range
Dim rList As Range
Dim rBTD As Range
Dim rCell As Range
Dim rFound As Range
Dim i As Long
Dim lCellCnt As Long

With ThisWorkbook.Worksheets("employees")
Set rDist = .Range("districtnumber")
Set rList = .Range("list")
Set rBTD = .Range("branchtodistrict")
End With

lCellCnt = rList.Cells.Count

For i = lCellCnt To 1 Step -1
Set rCell = rList.Cells(i)
Set rFound = rBTD.Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then
If rFound.Offset(0, 1).Value < rDist.Value Then
rCell.EntireRow.Delete
End If
Else
rCell.EntireRow.Delete
End If
Next i

End Sub

When you delete cells that are in the a range that is part of the loop, you
have to loop from bottom to top. I don't think that's the whole issue,
because that usually will not delete cells that it's supposed to as opposed
to deleting too many cells. Anyway, the above the should work. Let me know
if it doesn't.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Marie" wrote in message
...
Thanks so much for looking at this!

the 'list' is the column of branch numbers I am downloading that I want to
choose/delete from - the 'branchtodistrict' is a column that has all the
possible branch numbers from all the districts(the range I named
'branchtodistrict' is only one column, in the next column I have the
corresponding district numbers, but I didn't include this column in the
named range, is that my mistake?) - the 'district number' is a cell I named
that will always have the correct district number - the number for the file
I am calculating data for. I want to look at the district number and pull
from the 'list' only corresponding branch numbers, if it doesn't go with
that district then delet it.

Am I all wet on my code? I'm new at this but really trying...

"Dick Kusleika" wrote:

Marie

Need more info. What range is "list"? What range is "BranchToDistrict"
and
"DistrictNumber"?

If c.Offset(0, 1).Value =
Range("DistrictNumber").Value
Then
Offset(0, 1).Value
Else: Selection.EntireRow.Delete

End If

If every row is being deleted, then this is probably always false. The
THEN
part of this doesn't make any sense. If "List" comprises more than one
column, that's probably the problem.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Marie

I tried it and it doesn't work. I think the problem is that I didn't
explain that the named range 'district number' is on a sheet named
historical and the 'branchtodistrict' is on another sheet named branches.
The only named range on the employees sheet is the 'list'. I thought as long
as the neamed ranges were in the same file it didn't matter. Like I said I
am new so the only reason I think this might be the problme is because of
the line that says 'with this workbook.worksheets("employees") But they
are all in the same file, is that considered the workbook? Can it be fixed?
I have tried but no luck. Please....

Names can be Workbook level names or Worksheet level names. I don't know
which yours are, but this should handle either situation


With ThisWorkbook.Worksheets("employees")
Set rDist = .Range("districtnumber")
Set rList = .Range("list")
Set rBTD = .Range("branchtodistrict")
End With

Change to

With ThisWorkbook
Set rDist = .Sheets("historical").Range("districtnumber")
Set rList = .Sheets("employees").Range("list")
Set rBTD = .Sheets("branches").Range("branchtodistrict")
End With

If that doesn't work, be specific about what doesn't work. If you're
getting an error, say what the error is. If you're just getting bad
results, then include what you expect them to be and what they are.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com