RecordCount function is giving me a Run-time error'1004': Method 'Range' of object'_Global' failed in excel

Question

ActiveWorkbook.Names.Add Name:="RecordCount", _
                 RefersToR1C1:="=qryTC2015ContainerUtilizationSh!R1C100"

    Range("CV1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(C[-99])"

    RecordCount = Range("RecordCount")
    
    
    
    
    Range("A2:N" & RecordCount).Select
    
    'Range(Selection, Selection.End(xlDown)).Select
    
    Selection.Copy
    
    Windows("TC Containers Shipped Report Template.xlsm").Activate
          
    Sheets("Container Utilization").Select

    Range("A2").Select

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

what would the problem be and how can it be fixed?

[Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). After that, see [How to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Sep 02 '21 at 16:55

score 0 · Answer 1 · answered Sep 03 '21 at 12:51

It looks like you are expecting a number out of that line and all you are doing is referring to a range. If you wanted the number of records you would need it to be `Range("RecordCount").Count", which will return the number of cells in the range, not the number of cells with values. If you want the number of records in column N, it is better to find the last row as previously suggested.

Dim LastRow as Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row

Range("A2:N" & LastRow).Copy

Workbooks("TC Containers Shipped Report Template").Sheets("Container Utilization").Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

Your welcome. Please accept the answer to close out the question. — Darrell H, Sep 03 '21 at 15:52

RecordCount function is giving me a Run-time error'1004': Method 'Range' of object'_Global' failed in excel

1 Answers1