Solved: phantom name ranges

[artds]

HI all!!

I have this minor problem which just seemed to bug me, was wondering if any of you can help. Please take a look at the following codes:





Sub ExpandTables()
Dim TblNm As Range, n As Name
Dim cnt As Integer
Dim ws As Worksheet

Set ws = Sheet9
ws.Activate
cntDistiRow = Cells(1, 1).Value

For Each n In ws.Names
Set TblNm = Range(n)
cnt = TblNm.Rows.count
Do While Not (cnt - 2) < cntDistiRow
TblNm.Rows(cnt).EntireRow.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
cnt = cnt + 1
Loop
Next n

End Sub




Now, there's no problem with the code... Just that, in the VBE window, i use the 'f8' key to step in to the procedure, and when line reached to "Set TblNm=range(n)", and i point my mouse cursor to 'n', it indicate a name range, "=summary!$A$13:$A$15".

Now the thing is, I've looked through all the sheets in that workbook, and there are no such name ranges. I definately did not define that particular name range, so how did it get there? I want to delete the name range, but in the "Define Name" window, I cannot find such name. When I tried to delete the above mentioned cells, and again 'f8' the procedure, it indicate "=summary!#REF!". However rows are still inserted to that particular range.

There are no other open workbooks, and no hidden sheets. So I'm just wondering if theres anyone who can help me overcome this problem. As If i leave it as it is, the code will ruin my entire format. as there are other name ranges, which I did add myself, in the rows below.


Thanks all in advance
regards
artds

[Simon Lloyd]

The range you are given is the range that Excel performed a filtering effect on, try it on another sheet enter some data run your code with F8 you will find it skips straight to End Sub now use autofilter on the columns filter the data turn it back off and get rid of the filters now run your code, you will see in is now the value of the first cell of the first column you filtered to the last row that had data in it!

[RichardSchollar]

Hi

Names can be hidden so that they are not visible in the Define Names dialog.

Try running something like:

[vba]Set ws = Worksheets.Add
i = 1
For Each nm In ThisWorkbook.Names
With ws
.Cells(i,1).Value = nm.Name:.Cells(i,2).Value = nm.RefersTo:.Cells(i,3).Value = nm.Visible
End With
i=i+1
Next nm[/vba]

to give you a list of names in the workbook and indicate what they refer to and whether they are visible or not.

I sometimes use Charles Williams/Jan Karel Pieterse's Name Manager add-in which is very useful - downloadable here:

http://www.jkp-ads.com/officemarketplacenm-en.asp

Richard

[artds]

Hi Simon!

I understand now. Thanks a lot! Its because of the auotfilter. That particular name range points to the autofiltered table, from first cell to last cell. Great.


Hi Richard!
I tried your codes and it did print out an additional 3 name ranges that was hidden and which Simon was talking about. Thanks! I've already downloaded the software that you pointed out! Along with the ASAP software that I have, this Name manager works out great!

Looks like I still have a long way to go if I ever want to be in your level. hmmm.... maybe make in another milenia.
hee... thanks a million!


regards
artds

[Bob Phillips]

I sometimes use Charles Williams/Jan Karel Pieterse's Name Manager add-in which is very useful - downloadable here:

http://www.jkp-ads.com/officemarketplacenm-en.asp

Only sometimes? I find I cannot work without nowadays. That, MZ-Tools, and Rob Bovey's code cleaner are 3 must haves IMO.

[Simon Lloyd]

Only sometimes? I find I cannot work without nowadays. That, MZ-Tools, and

Rib Bovey's code cleaner

You Bob?, You need code cleaner?...........another urban myth dispelled!

[Bob Phillips]

Dirtiest code in town mate.

[RichardSchollar]

Only sometimes? I find I cannot work without nowadays. That, MZ-Tools, and Rob Bovey's code cleaner are 3 must haves IMO.

I'm not allowed downloads at work (which is pretty much where I do all my Excel stuff) hence I have no access to it.

So, until I make the big move out of regular employment ...

Dirtiest code in town mate.

So I've heard - oh, wait! You said 'code' not 'mind'

[Jan Karel Pieterse]

Richard:

Drop me a line if you want me to mail the tool to you directly. It is a regular Excel addin, so no installation needed.
i n f o a t j k p - a d s . c o m (get rid of spaces and change at to @)

[artds]

Only sometimes? I find I cannot work without nowadays. That, MZ-Tools, and Rob Bovey's code cleaner are 3 must haves IMO.

Speaking of which, the code cleaner, I do have it as well, along with , mz tools, document project, ASAP, and now name manager, How does the code cleaner works? Does it removes the comment lines? or does it compress code lines? or does it remove unused/undeclared variables?


regards
artds

[Bob Phillips]

none of those. When you develop code in VBA, the workbook tends to bloat. Code Cleaner just exports all code modules and re-imports them, which seems to remove that bloat. Very simple, but very effective.

[artds]

oh..... so das ne...(japanese for 'I see...'). Now I get it. Obrigado xld