Worksheet Change or SelectionChange Event?

[UNDPC]

Hi,
I have an excel worksheet that uses a cell ("B3") as the parameter passed to the query in MS Query. When that value is changed, the query is run for that value and the data is displayed on the same worksheet. After the query is run, I want to hide all the rows that have empty data before the total row several rows below. I am a little confused as to which Change event I should use for this and also the syntax of the Change function. Can anyone help with this?

 

[PHV]

Why is the total row several rows below ?
You may consider make it adjacent to the QueryTable range and modify the FillAdjacentFormulas property of the data range.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

scratch this, i have it working for the most part.

2nd question:
Is there a way to run a macro after all the calculated cells on the sheet are done calculating?

 

[PHV]

Why not write a macro that call the Refresh method of the QueryTable with BackgroundQuery:=False and then do the stuff you want ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UNDPC]

the query is run with a cell on the sheet being used as the parameter, so when you change the cell value the query runs and the data is passed back to the same worksheet, a couple of rows below. so i wanted to try the worksheet change event, but it runs it twice, once right after i change the value, then after the data has been passed back. so i figured that maybe i could try it after all the cells are calculated on the worksheet. Is there a way to do this? I am fairly new to VBA, so i don't know too much about the background query stuff.

 

[PHV]

Don't know if the Workbook_SheetCalculate event will be triggered in your case.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244