Gustav Brock
Gustav at cactus.dk
Mon Jan 23 08:52:08 CST 2006
Hi Lambert Well, that's what I thought, but I ran the code for "A1" where A1 to B4 were filled in, but only A1 itself was selected. Tried both in E97 and E2003. /gustav >>> Lambert.Heenan at AIG.com 23-01-2006 15:31:54 >>> Thanks for your feedback Gustav. I think what you are saying is that if you know in advance what cells the named range covers you can pass a range address in in the parameter sRegionStart with a value like "F5:J7" of "$G$5:$J$7" and then your variation on the code will assign a name to that range. However, my routine was written to handle the situation where you do not know the extent of the range in advance. Instead you only know for sure that some cell is within the range. Thus my code passes in an address in sRegionStart like "A1", and then the execution of the line xlApp.ActiveSheet.Range(sRegionStart).Select causes Excel to highlight all the cells in the continuous (strictly - 'contiguous') region that includes the cell already selected (A1). The subsequent line of code rng = xlApp.Selection.CurrentRegion.Address Then retrieves the address of the complete region and uses this to set the named range address. All the highlighting and selecting usually takes place while the workbook is opened in hidden mode. Lambert