Solved

Cannot delete range names with Excel VBA

Posted on 2011-03-22
29
744 Views
Last Modified: 2012-05-11
I am processing about 75 workbooks from our intranet.  There is one worksheet for each that is going into a summary workbook, which then has a Table of contents Created.  this is done from an Excel application that pulls the workbook, creates the destination workbook and copy/pastes the 75 into the destination, then creates a table of contents.  As the process copies the worksheet into the destination, it creates a named range of the data based on a particular naming convention.  However, at the ends of the process when the destination workbook is completely created, I want to go through and delete all range names that beging with GP.  However, I cannot get it do to this.  I can delete all range names, but I don't want that, I only want to delete particular range names.  HOwever, what I have simply ignores those ranges with GP as the first two letters and I simply cannot understand why.  I tried converting the name to a string for testing, but that did not work either
Sub DeleteNamedRanges(strWbkName As String)
'/******************************************************************************
' Clears out all the unnecessary named ranges in the workbook.  GP is the prefix
' used by the Green Package
' DO NOT USE GP to start range name otherwise it will be DELETED!
'/******************************************************************************
Dim MyName As Name
Dim intText As Integer
Dim strName As String
Workbooks(strWbkName).Activate

    For Each MyName In Names
    If Left(MyName.Name, 2) = "GP" Then
        MyName.Delete
    End If
    Next MyName
End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 12
  • 9
  • 7
29 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192231
Try this

For Each MyName In Workbooks(strWbkName).Names

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35192310
also, you could check if all the sheets are unprotected... these names are defined in a worksheet scope, and protected sheets dont let you delete names from them.

Sid's suggestion is also good, because you'll be forcing Excel to delete the names from that specific destination workbook.
0
 

Author Comment

by:ssmith94015
ID: 35192448
I change it to the attached, but still does not recognize the range name.
Sub DeleteNamedRanges(strWbkName As String)
'/******************************************************************************
' Clears out all the unnecessary named ranges in the workbook.  GP is the prefix
' used by the Green Package
' DO NOT USE GP to start range name otherwise it will be DELETED!
'/******************************************************************************
Dim MyName As Name
Dim intText As Integer
Dim strName As String
Workbooks(strWbkName).Activate

    For Each MyName In Workbooks(strWbkName).Names
    If Left(MyName.Name, 2) = "GP" Then
        MyName.Delete
    End If
    Next MyName
End Sub

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192464
Try this

If UCase(Left(Trim(MyName.Name), 2)) = "GP" Then

Sid
0
 

Author Comment

by:ssmith94015
ID: 35192692
Still won't work.  However, I did a debug.print  of

Debug.Print "Just MyName: " & MyName
 Debug.Print "MyName and Name: " & MyName.Name

ANd it returned this, so I think it simply is not getting to the GP part of the data
Just MyName: ='SECTOR-SWTACTC'!$A$6:$BQ$51
MyName and Name: 'SECTOR-SWTACTC'!GPData
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192700
Oh Ok got it....

Give me few moments.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192724
Try this

Sub DeleteNamedRanges(strWbkName As String)
    Dim MyName As Name
    Dim intText As Integer
    Dim strName As String, MyArray() As String
    
    Workbooks(strWbkName).Activate

    For Each MyName In Workbooks(strWbkName).Names
        On Error Resume Next
        MyArray = Split(MyName.Name, "!")
        On Error GoTo 0
        If UCase(Left(Trim(MyArray(1)), 2)) = "GP" Then
            MyName.Delete
        End If
    Next MyName
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192736
Sorry Use this

Sub DeleteNamedRanges(strWbkName As String)
    Dim MyName As Name
    Dim intText As Integer
    Dim strName As String, MyArray() As String
    
    Workbooks(strWbkName).Activate

    For Each MyName In Workbooks(strWbkName).Names
        On Error Resume Next
        MyArray = Split(MyName.Name, "!")
        If UCase(Left(Trim(MyArray(1)), 2)) = "GP" Then
            MyName.Delete
        End If
        On Error GoTo 0
    Next MyName
End Sub

Open in new window


Sid
0
 

Author Comment

by:ssmith94015
ID: 35192905
I have attached a screen shot of some debugging.  It recognizes the GP now, but is not deleting properly Debug Screen
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35192970
Ok try this

Replace

MyName.Delete

with

Workbooks(strWbkName).Names(MyName.Name).Delete

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193008
If myname.name like '*!GP*' then
myname.delete
End if
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193024
use quotes not apostrophy
0
 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 250 total points
ID: 35193104
This is Sid's code (credit for him, please!)
but I changed somethings based on my experience:
1) never define error treatment inside the loop
2) instead of using array, i removed the array and changed the condition
Sub DeleteNamedRanges(strWbkName As String)
On Error Resume Next
    Dim MyName As Name
    
    Workbooks(strWbkName).Activate

    For Each MyName In Workbooks(strWbkName).Names
        If UCase(Left(Trim(MyName.Name), 2)) like "*!GP*" Then
            MyName.Delete
        End If
    Next MyName
End Sub

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 35193165
Sid, it deletes everyting again.  Fernando, tried that, but it did not work either.  I don't think there is a solution.  Either it deletes everything or nothing.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193176
Can you upload a sample file?

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193197
can you post the resulting file here ???
I want to try some ideas... Sid will also have fun with it...

<email removed by SouthMod>
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193214
>>>if you prefer to send to email, because of confidentiality, <edit>

FernandoFernandes: I am afraid that is not allowed in EE :(

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193230
damn... it's ok ... sorry EE moderator, it won't happen again.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193257
FF: I also learnt it the hard way :(

ssmith94015: If you want you can delete all the data from your workbook and then upload it.

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193261
smith,
make a copy of your file
run this code in it
and upload it to EE

sub RemoveAllContent()
dim sht as worksheet
application.enableevents=false
application.calculation = xlcalculationmanual
for each sht in activeworkbook
sht.unprotect
sht.cells.clearcontents
next
application.enableevents=true
application.calculation = xlcalculationautomatic
End Sub

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 35193403
Actually, I FINALLY GOT IT TO WORK!!!  Sid, a little variation on some ideas you gave me.
Sub DeleteNamedRanges(strWbkName As String)
'/******************************************************************************
' Clears out all the unnecessary named ranges in the workbook.  GP is the prefix
' used by the Green Package
' DO NOT USE GP to start range name otherwise it will be DELETED!
'/******************************************************************************
    Dim MyName As Name
    Dim strMyName As String
    Dim intText As Integer
    Dim intLength As Integer
    Dim strFinalName    As String

    Workbooks(strWbkName).Activate

    For Each MyName In Workbooks(strWbkName).Names
        On Error Resume Next
        strMyName = CStr(MyName.Name)
        intText = InStr(MyName.Name, "!")
        intLength = Len(strMyName)
        strFinalName = Right(strMyName, intLength - intText)
        If Left(strFinalName, 2) = "GP" Then
           Workbooks(strWbkName).Names(strFinalName).Delete
        End If
        On Error GoTo 0
    Next MyName

End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193434
Glad it's sorted :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193444
ssmith94015: I feel FF also worked on this and hence I wouldn't mind sharing points with him :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 35193463
Will do.  I will contact moderator and it is very gracious of you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193467
Thanks for understanding :)

Sid
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35193472
Thanks guys !
It's ok with the points, no need to worry about it.
:-)
0
 

Author Closing Comment

by:ssmith94015
ID: 35198084
Thank you both again..  This was a real show-stopper.  Sid, you have repeatedly saved my patootee on this project.  Fernando, look forward to more help from you!  This is all done in Excel and I usually program databases out of a server or ACCESS, so have been really challeneged on this and I appreciate the help.  There is still way more to do, so more questions will be coming soon to the EE near you!
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35198422
:-) Thanks a lot SSmith... Looking forward to trying helping you again !
p.s.: I like to be in topics where Sid is, i usually learn something new with him too :-)

Have a good one, and good luck to us all !
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
create excel pivot chart 12 43
Outlook 2012 VBA: Object missing 14 33
VBA Help 18 44
excel formula to sum column 13 15
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question