Runtime error 13 when setting entire row to range variable type

Question

I am trying to build a button to add a new line and copy features including a dropdown list and VLookup from a hidden sample row. I keep getting a runtime error 13 when I'm trying to assign the empty new row to a range variable (CelRange). The error occurs whenever the code is trying to assign a row to a range variable.

The form I'm building is a quote template for invoicing customers. Each row has a dropdown list of items we have in the inventory (data validation from an inventory list). Once the item is selected, the price will be sourced directly from another hidden pricing sheet. I have tried to narrow the range from the entire row to a specific range to no avail.

Here's the code I wrote. The debugger highlights the line with the #.

Sub InsertEquipmentLineAndFill()

    ' Define Variables
    Dim CelRange As Range
    Dim PastedRange As Range

    ' Move to the first column of the selected row then offset to the first cell in the row below
    Range(ActiveCell.EntireRow.Address)(1, 1).Activate
    ActiveCell.Offset(1, 0).Select

    ' Insert row ABOVE offsetted row
    ActiveCell.EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Set CelRange = Selection #This is where the debugger highlights

    ' Copy all formats and formulas from the hidden sample row
    Range("EquipmentLineSample").Select
    Selection.Copy

    ' Paste the copied sample row into the table
    CelRange.Select
    ActiveSheet.Paste

    ' Saving the position of the pasted row
    Set PastedRange = Selection

    ' Unhide the row
    PastedRange.EntireRow.Hidden = False

End Sub

The code used to work fine inserting a new line with the dropdown list, VLookup, and calculation formulas available. The end result should be a line where the team can select the item from the dropdown list, have the price directly sourced from our hidden pricing sheet, and only need to enter quantity to get a line item total.

However, now we are getting a runetime error 13 on the Set CelRange = Selection line and similar lines assigning a range variable in the code.

A great time to read up on [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Aug 27 '19 at 02:38
Try adding `Debug.Print TypeName(Selection)` just before the error and see what you get — Tim Williams, Aug 27 '19 at 03:16
Welcome to SO. What about `Set CelRange = Activecell.EntireRow`? — Foxfire And Burns And Burns, Aug 27 '19 at 08:54

chris neilsen · Accepted Answer · 2019-08-27T02:43:23.903

1

Relying on Selection/Active* is often unnecessary and error prone.

Eliminating the Select's your code can be reduced to

Sub InsertEquipmentLineAndFill()
    With ActiveCell.EntireRow
        .Offset(1).Insert CopyOrigin:=xlFormatFromLeftOrAbove
        Range("EquipmentLineSample").EntireRow.Copy .Offset(1)
        .Offset(1).Hidden = False
    End With
End Sub

_{Note that I can't reproduce your error, so am unsure if this will actually fix your error. But avoiding those Selects is always a good start}

edited Aug 27 '19 at 02:43

answered Aug 27 '19 at 02:40

chris neilsen

52,446
10
84
123

Thanks Chris, without question your code worked perfectly. It has been difficult to diagnose the problem since it seems to come and go. Avoiding "activate" and "select" does seem to do the trick! – Marty Aug 28 '19 at 16:19

Runtime error 13 when setting entire row to range variable type

1 Answers1