Run Time Error '1004': Select method of Range Class failed VBA

Question

I am getting a run time error on the below code , can you please assist. The part and getting the error on is rngRange.Select. Can you advise in any way in which i can amend the below code? Thank you in advance

Sub NameRangeTop(Optional ByRef rngRange As Range)

If rngRange Is Nothing Then
    Set rngRange = Application.Selection
Else
    rngRange.Select
End If

Dim ActiveRange As Range
Dim NumRows, NumColumns, iCount As Long

Dim CurSheetName As String
CurSheetName = ActiveSheet.Name

Set ActiveRange = Selection.CurrentRegion
ActiveRange.Select
NumRows = ActiveRange.Rows.Count
NumColumns = ActiveRange.Columns.Count

If NumRows = 1 And NumColumns = 1 Then
MsgBox "No active cells in the surrounding area. Try running the macro from a different location", vbCritical, "Local Range Naming"
Exit Sub
End If

If NumRows = 1 Then
    Set ActiveRange = ActiveRange.Resize(2)
    NumRows = 2
End If

For iCount = 1 To NumColumns
ActiveRange.Resize(NumRows - 1).Offset(1, 0).Columns(iCount).Name = CurSheetName & "!" & ActiveRange.Rows(1).Columns(iCount).Value
Next

ActiveRange.Resize(NumRows - 1).Offset(1, 0).Select

End Sub

user3598756 user3598756 · Accepted Answer · 2016-09-25T09:05:55

it's because the passed rngRangerange doesn't belong to currently active worksheet

code like this

If rngRange Is Nothing Then
    Set rngRange = Application.Selection
Else
    rngRange.Parent.Activate
    rngRange.Select
End If

Run Time Error '1004': Select method of Range Class failed VBA

1 Answers