Error 424 trying to determine last used row

[KentBurel]

I get this error:

When I press debug it points to the row that set's lastRow. This is a similar statement that I see in lots of books and articles for determining the last used row. I don't understand why this doesn't work.

Option Explicit
Option Base 1
Sub BuildBmdTable()
    Dim Precincts       As Variant
    Dim precinctRange   As Range
    Dim myBMDtable()    As Variant
    Dim Number_of_BMDs  As Integer
    Dim mySheet         As Worksheet
    Dim outRange        As Range
    Dim i               As Integer
    Dim j               As Integer
    Dim k               As Integer
    Dim totalRows       As Integer
    Dim lastRow         As Range
    Dim firstRow        As Range
  
    totalRows = 0
    k = 1
    Application.Calculation = xlManual 'Turn off calculations for a bit
  
' First set the Precincts named range
    Set mySheet = Worksheets("Constants")
    Set firstRow = mySheet.Cells(13, 1) ' Skip the header row
    Set lastRow = mySheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set precinctRange = Range("A" & firstRow & ":F" & lastRow)
    On Error Resume Next
        Names("Precincts").Delete ' Delete the "Precincts" named range
    On Error GoTo 0
    Precincts.Name = "Precincts" ' Add the "Precincts" name back
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="Precincts", RefersTo:= _
        precinctRange
    ActiveWorkbook.Names("Precincts").Comment = "Precinct table named r"
  
    For i = 1 To UBound(Precincts)
        totalRows = totalRows + Precincts(i, 5)
    Next i
    ReDim myBMDtable(totalRows, 6)
    Sheets("BMD Template").Copy After:=Sheets(1)
    Sheets(2).Name = "BMD Master"
    Sheets(2).Visible = True
    Set outRange = Sheets(2).Range("A3:F" & totalRows + 2)
  
    For i = 1 To UBound(Precincts)
        Number_of_BMDs = Precincts(i, 5) ' column 5 is the number of BMD for this precinct
        For j = 1 To Number_of_BMDs
      
            outRange.Cells.Item(k, 1).Value = Precincts(i, 1)
            outRange.Cells.Item(k, 2).Value = j
            outRange.Cells.Item(k, 3).FormulaR1C1 = "=RC[-2]&""_""&RC[-1]"
            k = k + 1
      
        Next j
    Next i
  
' Now set the BMDData range name
    On Error Resume Next
    ActiveWorkbook.Names("BMDData").Delete
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="BMDData", RefersToR1C1:= _
        "='BMD Master'!R3C3:R" & totalRows + 2 & "C6"
    ActiveWorkbook.Names("BMDData").Comment = "BMD table named range"

    Application.Calculation = xlAutomatic 'restore calculation setting
End Sub

 

[Trebor76]

Hi Kent,

When assigning a range variable you need a column and row reference where you've only passed in the row. While to find the last row in Col. A you can use this...

Option Explicit
Sub Macro1()

    Dim lastrow As Range
    Dim MySheet As Worksheet
   
    Set MySheet = Worksheets("Constants")
    Set lastrow = Range("A" & MySheet.Cells(Rows.Count, "A").End(xlUp).Row)
   
    MsgBox lastrow.Row

End Sub

...I would change the lastrow variable to long and set it this way:

Option Explicit
Sub Macro2()

    Dim lastrow As Long
    Dim MySheet As Worksheet
   
    Set MySheet = Worksheets("Constants")
    lastrow = MySheet.Cells(Rows.Count, "A").End(xlUp).Row
   
    MsgBox lastrow

End Sub

Hope that helps.

Robert