VBA Excel 97 : Adding a Range Name to Refer to Current Region - Please help

VBA Excel 97 : Adding a Range Name to Refer to Current Region - Please help

Post by ho.. » Tue, 23 Nov 1999 04:00:00



I am trying to write a macro that adds a range name that refers to the
current selection rather than a specific cell address region.

The preceding code in my macro is currently
Selection.CurrentRegion.Select; and I then want to make the Range Name
refer to this selection.

From Chip Pearson's marvellous website, I know that if the currently
selected region had address $a$1:$C$10; the required code would have
form

ThisWorkbook.Names.Add Name:="NewName", RefersTo:="=$A$1:$C$10"

Can anyone help with the code that is required to make the NewName refer
to the currently selected region rather than a specific address ?

Thanks In Advance

Andrew

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

VBA Excel 97 : Adding a Range Name to Refer to Current Region - Please help

Post by Manuela Geype » Tue, 30 Nov 1999 04:00:00


Hello,

I have copied part of an Excel macro which might be of help.
I have defined a range object, that refers to the current region (starting
from cell A1) and I use this range object in the 'Add range name' command.

Sub test()
Dim wkbwork As Workbook
Dim wkswork As Worksheet
Dim rngAllData As Range

Set wkbwork = ActiveWorkbook
Set wkswork = wkbwork.ActiveSheet

Set rngAllData = wkswork.Cells(1, 1).CurrentRegion
wkbwork.Names.Add Name:="AllData", RefersToR1C1:=rngAllData
End Sub

Good luck,
Manuela


> I am trying to write a macro that adds a range name that refers to the
> current selection rather than a specific cell address region.

> The preceding code in my macro is currently
> Selection.CurrentRegion.Select; and I then want to make the Range Name
> refer to this selection.

> From Chip Pearson's marvellous website, I know that if the currently
> selected region had address $a$1:$C$10; the required code would have
> form

> ThisWorkbook.Names.Add Name:="NewName", RefersTo:="=$A$1:$C$10"

> Can anyone help with the code that is required to make the NewName refer
> to the currently selected region rather than a specific address ?

> Thanks In Advance

> Andrew

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

1. VBA Excel 97 - Adding a Named Range - Please help

Dear All,

I am trying to write a macro that adds a range name that refers to the
current selection rather than a specific cell address region.

The preceding code in my macro is currently
Selection.CurrentRegion.Select; and I then want to make the Range Name
refer to this selection.

From Chip Pearson's marvellous website, I know that if the currently
selected region had address $a$1:$C$10; the required code would have
form

ThisWorkbook.Names.Add Name:="NewName", RefersTo:="=$A$1:$C$10"

Can anyone help with the code that is required to make the NewName refer
to the currently selected region rather than a specific address ?

Thanks In Advance

Andrew

2. debug commands ?

3. Excel Range names - refer to range on ALL sheets??

4. how to get these games working on the real thing

5. Referring To Named Range With VBA

6. ADB Keyboard on a Mac Plus?

7. Range.Consolidate in Excel 97 VBA

8. FileCompletion, specify the complement of a set?

9. Excel5 VBA Naming of Ranges - Help please

10. help with updating named range in Excel 7.0 using VBA

11. Range names in other files in Excel 97

12. Adding Excel 97 VBA Code to Existing Macros