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
Error.xls
Description: MS-Excel spreadsheet
