Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
oops
should be called as RemoveEmployees "John", 1 no brackets Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know cell's address from Excel Query
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to refer to a cell's address, not it's content... | Excel Discussion (Misc queries) | |||
Repeat: Active Cell's Address | Excel Programming | |||
cell's address of the largest number | Excel Discussion (Misc queries) | |||
How can I change a text to a cell's address? | Excel Discussion (Misc queries) | |||
How do I programmatically know the current cell's address | Excel Programming |