run time error 380 rowsource property invalid property value

[Patriot2879]

Hi good morning, Please can you help me, i get a 'Run time error 380 property invalid property value' and it highlights in yellow the line below, but i have no idea how to correct it as i cant see what the issue is

.RowSource = "Data_Display!A2:K" & lr

anf this is the whole code for my listbox -

Sub Refresh_Listbox()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
 
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")

''''''''''' Copy Data ''''''''''
dsh.Cells.Clear
sh.AutoFilterMode = False

If Me.cmb_Filter_By.Value <> "ALL" Then
    sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_Filter_By.Value, sh.Range("1:1"), 0), "*" & Me.txt_Search.Value & "*"
End If

sh.UsedRange.Copy dsh.Range("A1")

sh.AutoFilterMode = False

Dim lr As Long
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 11
    .ColumnWidths = "20,160"
    .RowSource = "Data_Display!A2:K" & lr
End With

End Sub

Really hope you can help me please.

 

[RoryA]

What is the actual value of lr at the time the error occurs?

 

[Patriot2879]

What is the actual value of lr at the time the error occurs?

Sorry I don’t know what you mean? I’m still learning vba, and got this code from somewhere else

 

[RoryA]

lr is supposed to be a row number. What value does it actually have (0, 1, 2 etc) when the code errors?

 

[Patriot2879]

It looks like it happens when the date is entered which is row E

 

[RoryA]

That doesn't answer my question. What is the actual value of lr when the code errors?

 

[Patriot2879]

That doesn't answer my question. What is the actual value of lr when the code errors?

I’m sorry I’m being thick I don’t understand

 

[RoryA]

lr is a variable which contains a number value. What is its actual value when the code runs and causes an error? Is it 1, 2, 3, 482 or something else?

This line:

.RowSource = "Data_Display!A2:K" & lr

is trying to assign a range to populate the listbox. It uses lr to determine the last row of that range so I am trying to determine if it is a valid number. If lr is 0, then the range would be A2:K0 which is not a valid range.

 

[Patriot2879]

I have attached a screenshot of the page it links into, does this help? sorry im not being very helpful.

 

[RoryA]

No, you haven't attached anything.

I just need to know what the value of the variable is. Add a line before the Rowsource line that is just:

Msgbox lr

and run the code again and tell me what the message says.