question regarding external data range

[EdStevens]

Excel 2003 on XP Pro

Every day I receive a text file with a single record (space delimited). I
have written a macro to load that record into the next row of the worksheet,
using Get External Data. All that is working fine, but in doing initial
testing and debugging of the macro, I noticed that every time I bring in a
new record, it creates a new external data range. I really don't need these
defined data ranges, I just need the values loaded in. Really just a
housekeeping issue ... several months from now, when I have 100+ rows of
data, do I really want to have 100+ different external data ranges defined?
Or am I obsessing about a non-issue? Is there some way to clean-up/delete
the range definitions without deleting the data (cell values) itself?

 

[affordsol]

Hello,

You'll find below some code which should help you ... someway.

Glad to help+


'===ROUTINES===ROUTINES===ROUTINES===ROUTINES===ROUTINES
Sub ShowAllNamedRanges()
Dim lngNamesCount As Long
Dim intPtr As Integer
Dim strNamedRanges As String

'Get the nr of named ranges
lngNamesCount = Application.Names.Count
'Empty the strNamedRanges text
strNamedRanges = ""
For intPtr = 1 To lngNamesCount
strNamedRanges = strNamedRanges & vbCrLf & intPtr & " = " &
Names(intPtr).Name
Next intPtr
MsgBox strNamedRanges
End Sub
'===ROUTINES===ROUTINES===ROUTINES===ROUTINES===ROUTINES
Sub DeleteOneNamedRange()
Dim lngNamesCount As Long
Dim intPtr As Integer
Dim strNamedRanges As String

'Get the nr of named ranges
lngNamesCount = Application.Names.Count
'Empty the strNamedRanges text
strNamedRanges = ""
'Put list of Named Ranges in strNamedRanges
For intPtr = 1 To lngNamesCount
strNamedRanges = strNamedRanges & vbCrLf & intPtr & " = " &
Names(intPtr).Name
Next intPtr

Dim strPrompt, strTitle, strDefault, strConfirm As String
Dim intIndexOfRangeToBeDeleted As Integer
strTitle = "Delete one Named Range"
strDefault = ""
strPrompt = strNamedRanges & vbCrLf & "Input the index of the Named Range
to be deleted ..."
intIndexOfRangeToBeDeleted = Val(InputBox(strPrompt, strTitle, strDefault))

If intIndexOfRangeToBeDeleted > 0 Then
strTitle = strTitle & " CONFIRM..."
strPrompt = "DELETE " & Names(intIndexOfRangeToBeDeleted) & vbCrLf &
"<Y>es or <N>o..."
strConfirm = InputBox(strPrompt, strTitle, strDefault)
If Left(strConfirm, 1) = "Y" Then
Names(intIndexOfRangeToBeDeleted).Delete
End If
End If
End Sub
'===ROUTINES===ROUTINES===ROUTINES===ROUTINES===ROUTINES

 

[EdStevens]

That gave me what I needed. Thanks for the help.

 

[EdStevens]

Well, that was essentially what I needed .... ;-)

After playing around with the code you provided and seeing how it works,
for my purposes I reduced it to

'===ROUTINES===ROUTINES===ROUTINES===ROUTINES===ROUTINES
Sub DeleteAllNamedRange()
Dim lngNamesCount As Long
Dim intPtr As Integer

'Get the nr of named ranges
lngNamesCount = Application.Names.Count

For intPtr = 1 To lngNamesCount
Names(intPtr).Delete

Next intPtr

End Sub
'===ROUTINES===ROUTINES===ROUTINES===ROUTINES===ROUTINES

Then, on the first run, with 18 named ranges, when intPtr reached 10, it
returned 'subscript out of range'. That left me with 9 named ranges.
Running again from this point returned 'subscript out of range' when intPtr
reached 6, leaving me with 4 named ranges. Again when intPtr reached 3 ....
After several reps, all of the named ranges were gone, so I tried the 'real'
test of calling this routine as the last step of the routine that gets the
external data. On this run the newly created named range was deleted, but
the macro still returned the 'subscript out of range' error.

Ideas?

 

[affordsol]

Hello,

There's a caveat with the deletion of all the names from 1 to lngNamesCount.

Think of a list of 10 elements, each numbered from 1 to 10.

If you delete item #1, the item#2 will now be #1 ... and so on.

So, according to me, the loop you want is:

For intPtr = lngNamesCount To 1 Step -1
Names(intPtr).Delete
Next intPtr


always glad to help...

 

[affordsol]

Sorry Ed,

I didn't realize you wanted to delete ALL named ranges at once :
here's the solution

Sub DeleteAllNamedRanges()
Dim objName As Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

regards+