functions

Jackeb

Board Regular
Joined
Mar 20, 2002
Messages
81
there is a really useful function that you can use in VBA called 'current region'.

is there an equivalent in the actual worksheet functions of excel??

Cheers
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi Jackeb...
I could find nothing, however, if you will give some details as to what you are trying to accomplish, you might be surprised at the functions some of these guys come up with...
Tom
 
Upvote 0
Cheers Tom,

I too canne find nowt, grrr

basically i have been using the dynamic name ranges so loved by Oz who currently frequents this bord, the only problem is that the offset method is a little seams to get a little upset because my dynamic range is part of a query, when you refresh the queery the range only selects the refreshed bit, not the refreshed bit and adjacten cells, so I am looking for a really simple version, ie the current region method, to select the block that i would like to be my named range.
any suggestions?
 
Upvote 0
If I am catching on here, could you not run your query in code and then utilize the current region property?
Tom
 
Upvote 0
tom there is one reason for this, I would like to stress it is not my doing and nothing i can do about it.... here is an example of the sql....

SELECT Plant1_RDef.TagName, Plant1_RDef.Description, Plant1_AllReadings.DateTime, Plant1_AllReadings.pValue
FROM `\EUDIMS07RMTA PMPACKAGEDatabaseGas Compression`.Plant1_AllReadings Plant1_AllReadings, `\EUDIMS07RMTA PMPACKAGEDatabaseGas Compression`.Plant1_RDef Plant1_RDef
WHERE Plant1_RDef.RDefID = Plant1_AllReadings.RDefID AND ((Plant1_RDef.Description='Number Of Fired Starts (CNTR_01)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Hours Run (TIMR_01)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Gas Generator Speed (TNH_PR)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Ambient Temperature ITDP') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Inlet Temperature (CTIH)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Inlet Pressure (CIP)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Outlet Temperature (CTD)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Outlet Pressure (CPD)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='PT Exhaust Temp (TTXC)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='P3190 1st Stage Inlet Filter dp') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='P3191 2nd Stage Inlet Filter dp') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='PI3186 Comp Discharge Pressure') AND (Plant1_RDef.MachineID=2))
ORDER BY Plant1_AllReadings.DateTime, Plant1_RDef.Description


AAAAAAAAAAARrrrrrrrrrrggggggggggghhhhhhh

I hate people that don't think into the future when developing databases......


Ed
 
Upvote 0
On 2002-04-16 00:36, Jackeb wrote:
there is a really useful function that you can use in VBA called 'current region'.

is there an equivalent in the actual worksheet functions of excel??

Cheers
Hi Jackeb,
Maybe I can help you,
There is an icon to "Select Current Region", and you can put it on your toolbar (it looks like an X).
You'll find it by Toolbars | Customize | Commands | Edit | select Current Region.
Eli
This message was edited by eliW on 2002-04-16 01:26
 
Upvote 0
I never knew that....

the problem is that it returns the current region command in code rather than a worksheet command that i can use for the dynamic range in the name/define bit!?!?

Ed
 
Upvote 0
I should be more specific,

is there an alternative way of using the offset function?

Cheers

ED
 
Upvote 0
On 2002-04-16 00:36, Jackeb wrote:
there is a really useful function that you can use in VBA called 'current region'.

is there an equivalent in the actual worksheet functions of excel??

Cheers

Jackeb, 'Current Region' is implemented as an Excel command rather than a built-in Excel function. As previously stated it can be invoked using the 'Select Current Region' tool bar button, but it's origination is the Edit | Go To... | Special... set of selections.
 
Upvote 0
cheers,

I think i have solved the problem now. I used the offset function and the count function and it worked in a dynamic range.

Cheers
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top