I try to get cell's address through Excel Query to delete the range
manually.
For example

SELECT Emp_Name FROM Employee
WHERE Emp_ID='1'

I want to know where the data located in Excel, so i can delete the
range that contain the data. Is it possible?

Thanks,

Resant

Hi
You need to filter the Employee with Emp_Name and Emp_ID = "1". Record
a macro while you do this manually and you should have the code. Also,
Google this group for "filtering a range using 2 criteria"
regards
Paul

Hi Paul,

I can't use filter, cause there's others data beside "Employee" Range.
So I must get the Cell Address where Emp_Name located.
For example, Emp_Code located in $C$4, i will use the Cell's Row with
the code :

Sub Delete()
Intersect(Range("Employee"), Range($4:$4)).Select
Selection.Delete Shift:=xlUp
End Sub

Please help me, any example will be appreciated

Thanks

Hi
Your delete sub will only delete the cells in Range("Employee"), so I
don't understand why you cannot combine this with Filter.

Suppose Employee Name is in column 1 and Emploee ID is in column 2

Public Sub RemoveEmployees(Emp_Name as String, Emp_ID as Integer)
'Remove any filter present on the Range
'Filter by Name and ID for your values Emp_Name and Emp_ID, delete
entries and show remaining
'Range("Employee") should not include a Heading. If it does, use
Range("Employee").offset(1,0)
With Range("Employee")
.Parent.AutoFilterMode = False 'Removes drop down arrows
.AutoFilter Field:=1, Criteria1:=Emp_Name 'column 1
.AutoFilter Field:=2, Criteria1:=Emp_ID 'column 2
.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
.Parent.AutoFilterMode = False 'Removes drop down arrows
End With

End Sub

Calling RemoveEmployees("John", 1) will remove the rows in "Employees"
with name in column 1 of "John" and ID number in cloumn 2 of 1.

regards
Paul

oops
should be called as
RemoveEmployees "John", 1

no brackets
Paul

Great!
I've got your idea now...

But you can not delete the cells when AutoFilter is still active, this
will delete the entire row.
So, get the address, off the filter and delete the desire cells, right?
Thanks a lot!