How to know cell's address from Excel Query

[Resant]

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

 

[paul.robinson]

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

 

[Resant]

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

 

[paul.robinson]

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

 

[paul.robinson]

oops
should be called as
RemoveEmployees "John", 1

no brackets
Paul

 

[Resant]

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!

 

[paul.robinson]

Hi
It will only delete the cells within Range("Employee") and move the
cells below the deleted ones up. If Range("Employee") is ten columns
wide then all ten cells in the row will be deleted. If you don't want
all ten deleted then use Clear instead of delete to empty the cells
rather than remove them.

regards
Paul

 

[Resant]

Hi Paul,
Actually that is what I want, delete only the entire Range("Employee"),
that's why i use .Delete Shift:=xlUp.
But, now I've the problem. All of this must be done in a closed
workbook, is it possible to delete a range in closed workbook without
open it first?

Thanks,

Resant

 

[paul.robinson]

Hi
You can read from a closed workbook, but not change it, as far as I
know. If you really need the workbook closed, you should think about
moving to Access or some such.
You might be able to hide the fact that the workbook is open. You could
save it as an AddIn file, so it is not visible when opened - sounds a
bit "tricksy" though. You could also have the code open the file, do
the changes then save and close it with ScreenUpdating off. The file
would register as being open briefly though.
Call again if you want some file open/close code.
regards
Paul

 

[Resant]

I'm interested 'bout the AddIn. But actually I do many queries in that
closed workbook.
Some google user say that do query in opened workbook will cause bad
performance.
So, if I use code to open file, delete range and save file, didn't it
will cause leak of memory?
Any other solution how to delete range in closed workbook without open
it first?

Thanks,

Renata

 

[paul.robinson]

Hi
You can do a query if it is reading and the file is closed. You cannot
change the excel file without opening it though. The memory leak thing
might only happen if you don't release objects in the code (e. Set
myworkbook = Nothing) when you are done. It has nothing to do with
opening the file.
regards
Paul