You can put this in a regular sub or this worksheet double click event
Right click sheet tab>view code>insert this>double click any cell to name range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
For Each N In ActiveWorkbook.Names
If InStr(1, N.RefersTo, ActiveSheet.Name, vbTextCompare) > 0 Then
Set X = Intersect(ActiveCell, Range(N.RefersTo))
If Not X Is Nothing Then
fc = Range(N).Cells(1, 1).Address
mc = Range(N).EntireColumn.Address
N.RefersTo = "=OFFSET(" & fc & ",,,COUNTA(" & mc & "))"
End If
End If
Next N
End Sub
From: Daniel
Sent: Tuesday, August 30, 2011 4:34 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Named Range to Dynamic with VBA
Hi,
Be cautious with this macro and keep a copy of your workbook. Look at the
attached workbook in which there is an example of error.
Sub test()
Dim N As Name, Var As String, Tabl As Variant
For Each N In ActiveWorkbook.Names
If LCase(Left(N.RefersTo, 7)) <> "=offset" Then
Tabl = Split(Right(N.RefersTo, Len(N.RefersTo) - 1), ":")
Var = Range(Tabl(0)).Resize(, 1).EntireColumn.Address
N.RefersTo = "=OFFSET(" & Tabl(0) & ",,,COUNTA(" & Var & "))"
End If
Next N
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De la
part de Cab Boose
Envoyé : lundi 29 août 2011 23:39
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Named Range to Dynamic with VBA
Hi
I would like to select an already named range and using a command button
convert it to dynamic named range by columns and rows.
Is this possible ? It would be a lot quicker than doing it manually each
time. I failed to make a lot dynamic when I set them up.
Appreciate your help, I am aged !! (67) and do part time work just a few hours
a week for a small business. Love Excel.
Thanks and regards
Charlie Harris
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
