CODEWITHSUNDEEP
excelvba
Badja
Badja

Reputation: 875

Named Range Can't Be Deleted After Small Code Change

I recently split my code into two sections to stop the date automatically being entered, as on a Monday, we need to do 3 days worth of data

All I did was Add a new sub and redefine the variables - now i can't delete a named range

My code:

Option Explicit
Sub Import()

Dim ws As Worksheet, lastRowC As Long

Set ws = Worksheets("Report")
lastRowC = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row + 1 ' bottom populated cell of Column "C", plus 1

    With ws.QueryTables.Add(Connection:= _
            "TEXT;N:\Operations\001 Daily Management\Cemex\FMSQRY.CSV", Destination:= _
            ws.Cells(lastRowC, 3))
        .Name = "FMSQRY"
        ' etc
        ' etc
        .Refresh BackgroundQuery:=False
    End With

    With ActiveWorkbook
        .Connections("FMSQRY").Delete
        .Names("FMSQRY").Delete
    End With

End Sub




Sub TodaysDate()

Dim ws As Worksheet, lastRowC As Long, lastRowH As Long

Set ws = Worksheets("Report")

lastRowH = ws.Cells(ws.Rows.Count, 8).End(xlUp).Row + 1 ' bottom populated cell of Column "H", plus 1
lastRowC = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row ' bottom populated cell of Column "C"

    With ws.Range(ws.Cells(lastRowH, 8), ws.Cells(lastRowC, 8))
        .FormulaR1C1 = "=TODAY()"
        .Value = .Value
    End With

End Sub

So nothing to do with the Named Range was actually touched

.Name = "FMSQRY" still names my range, but when .Names("FMSQRY").Delete comes around I get a 1004 Error

Named Range

Debug

ANSWER:

With ActiveWorkbook
    .Connections("FMSQRY").Delete
    With ws
        .Names("FMSQRY").Delete
    End With
End With

Upvotes: 2

Views: 90

Answers (3)

Error 1004
Error 1004

Reputation: 8240

Try the below code without the .connections:

Option Explicit

Sub test()

    With ThisWorkbook
        .Names("FMSQRY").Delete
    End With

End Sub

Upvotes: 1

EvR
EvR

Reputation: 3498

Your Name is on sheet-level and not Workbook-level.(you could have the same name on different sheets)

so:

ActiveWorkbook.Worksheets("Report").Names("FMSQRY").Delete

Upvotes: 2

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

I am not sure why that code doesn't work.

But if you write code like below then it works...

Dim nm As Name
For Each nm In ActiveWorkbook.Names
    If nm.Name = "FMSQRY" Then nm.Delete
Next nm

Upvotes: 2

Related Questions