VBA Selecting a a range for a combobox input-CodePudding

I'm attempting to set the values shown in a combo box to the data shown in a single column of a filtered list so that it can be changed as required. Im running into error 1004 Application-defined object error when using a variable to define the range for the list items however.

the code i've written is:

Sub Vehicle_Catergory()

    Dim LastRow As String
    LastRow = Sheets("Vehicle_Data").Range("B2").End(xlDown).Address
    
    Sheets("marine Vehicle Selection").ListBox_Vehicle_selection.Clear
    
    'MsgBox LastRow.Address
    
    'Filters vehicle Data for vehicle Types
         Dim Criteria_1 As Range
           
        'selects the criteria
         Set Criteria_1 = Sheets("Config").Range("A3")
         
         'copies the filtered data to the destination
         With Sheets("Vehicle_data").Range("A2")
             .AutoFilter field:=1, Criteria1:=Criteria_1
         End With
    
    Sheets("marine Vehicle Selection").ListBox_Vehicle_selection.List = Sheets("Vehicle_Data").Range("B3:LastRow").SpecialCells(xlCellTypeVisible).Value


End Sub

the error occurs in the last line, at the LastRow variable, it works when replaced with a cell address but i need it to be able to vary with a changing list length. I have also tried setting lastRow to a range and using LastRow.adress to no avail and the same error

CodePudding user response：

A range needs Column and row for both start and end:

Sheets("marine Vehicle Selection").ListBox_Vehicle_selection.List = Sheets("Vehicle_Data").Range("B3:B" & LastRow).SpecialCells(xlCellTypeVisible).Value

Also, try this method of lastrow, this should work better with some cells being empty and thus giving you a false value of lastrow:

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Vehicle_Data") 'or declare Dim wb As Workbook and set it to what you need with ws then as Set ws = wb.Sheets("Vehicle_Data")
'or just skip the ws and use the Range immediately if your code is only used here
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

Hope that helps :)

CodePudding user response：

Could you test this?

Sub Vehicle_Catergory()

    Dim LastRow As Range
    LastRow = Sheets("Vehicle_Data").Range("B2").End(xlDown).Address
    
    Sheets("marine Vehicle Selection").ListBox_Vehicle_selection.Clear
    
    Sheets("Vehicle_Data").Range("B2").Select
    Selection.End(xlDown).Select
    Set LastRow = Selection
    
    'Filters vehicle Data for vehicle Types
         Dim Criteria_1 As Range
           
        'selects the criteria
         Set Criteria_1 = Sheets("Config").Range("A3")
         
         'copies the filtered data to the destination
         With Sheets("Vehicle_data").Range("A2")
             .AutoFilter field:=1, Criteria1:=Criteria_1
         End With
    
    Sheets("marine Vehicle Selection").ListBox_Vehicle_selection.List = Sheets("Vehicle_Data").Range("B3:" & LastRow.Address).SpecialCells(xlCellTypeVisible).Value


End Sub