Change Scope of Range with VB

[Nighabi]

I have some VB code that imports a file with the following code

Sub ImportFile()

Sheets(WEXImportSheet).Select
' Original Code to remove existing named range, variable didn't work so I did it in the case statement for variable definition
' ActiveWorkbook.Names(OpisFileName).Delete
Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & WEXImportFile, _
        Destination:=Range("$A$2"))
        .Name = WEXFileName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = StartRow
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = DataArray
        .TextFileFixedColumnWidths = FieldArray
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Later, I change the size of the range to include the first row, which is the header using the following code.

Sub ChangeRange()
'
' Deletes Original Named Range
' Creates new Named Range including header row

'
Sheets(WEXImportSheet).Select

Call RowCount

    With ActiveWorkbook.Worksheets(WEXImportSheet).Names(WEXFileName)
        .RefersToR1C1 = "=" & WEXImportSheet & "!R1C1:R" & Row & "C4"
    End With
End Sub
Sub RowCount()

' Gets number of Rows in the import file

Dim FoundData As Boolean
Dim CellName As String

Row = 1
Col = 1
FoundData = True
Do While FoundData
        Range("A1").Offset(Row - 1, Col - 1).Select
        CellName = ActiveCell.Value
        If Trim(CellName) = "" Then
            FoundData = False
            Exit Do
        End If
        Row = Row + 1
    Loop
    Row = Row - 1
End Sub

The problem I run into is the Named Range is distinct to the sheet but I need it to be a Workbook wide Named Range. I don't see anything that allows me to change the scope of an existing range short of deleting the one created on import and creating a new one.

 

[Jerry Sullivan]

The problem I run into is the Named Range is distinct to the sheet but I need it to be a Workbook wide Named Range. I don't see anything that allows me to change the scope of an existing range short of deleting the one created on import and creating a new one.

Hello Nighabi,

Your two functions to make the new named range with workbook scope could be replaced with something like this....(in place of NewName you could build your string with your variables).

Sub MakeRange_wHeaderRow()
    ActiveWorkbook.Names.Add Name:="NewName", _
     RefersTo:=Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

In addition to being more efficient by not stepping through each row, this code will get the entire data range based on the last row of data in Column A. The RowCount function you are using will stop at the first blank cell in Column A, even if there is data below that.

I don't think there is a way to change a named range from Worksheet scope to Workbook scope (other than deleting and creating a new range as you are doing).

Do you really need to delete the named range instead of just adding a new name that includes the header row? Doesn't deleting the name break your querytable?

 

[Nighabi]

Thanks for the reply.
I delete the old range simply because I want things to be neat. I took the code for the row count from older code that I needed row and column counts for CSV exports, I will use your method, much cleaner.
Jason