0

I've been wanting to show a filtered data on a listbox from the word that I search for from a table. Currently I have the following code:

'First, setting a as the word I want to search through the first column of the table'

Set a = Sheets("Main").Range("P14")

'setting Enertec Fee'

If a = "ENERTEC" Then

frmFeee.lstAHK.RowSource = "AHK_Fee!A6:D9"
frmFeee.lstMD.RowSource = "AHK_MD!A6:D9"
  
End If

This works but I realised that if I want to add new data on the table, it'll not be updated on the listbox unless I change the code everytime. Is there a better way of going about this? Any help would be greatly appreciated !

I've attached some pictures to help. listbox displaying results

Thank you.

FunThomas
  • 23,043
  • 3
  • 18
  • 34
Seok Ju Han
  • 13
  • 4
  • Does the range "AHK_Fee!A6:D9" contain the filtered data? or do you want to filter it? – Super Symmetry Jun 11 '21 at 06:26
  • No it's not filtered, they are just cluster of cells that correspond to Enertec,. What I try to do is that if I were to add a new data under the column heading ENERTEC, the range will have to change to something like "AHK_Fee!A6:D9, A13:D13" and I'm trying to avoid writing a new line of code every time. – Seok Ju Han Jun 11 '21 at 06:32
  • A bit more searching and you should find your answer. A web search for "vba find last used row" if your data is not in a table. Otherwise, if your data is in a table then you should find the solution here: https://stackoverflow.com/questions/18030637/how-do-i-reference-tables-in-excel-using-vb – 5202456 Jun 11 '21 at 07:56

0 Answers0