Only show values in userform that are within my specified area

[ipbr21054]

Hi,
I am using the code below.
Some info for you.
Table is called Table 2
Headers are in row 3
Row 4 is hidden.
My table is the range A to K
Values at present are A5 to K21 but will grow etc as time goes on.

The code shown loads the customers from the table & allows me to select & deletes a customer.

I make some notes outside of the table BUT when i open the userform these notes are also shown.
How can the code be edited so that only the values in the table are shown thus my notes are not loaded into the userform

See my example photo

Thanks

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim i As Long
  Dim lr As Long
  Dim sh As Worksheet

  Set sh = Sheets("GRASS")
  With ListBox1
    sh.Rows(.ListIndex + 5).Delete
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    If lr > 4 Then .RowSource = sh.Name & "!A5:B" & lr
    For i = 0 To .ListCount - 1
      If .Selected(i) Then .Selected(i) = False
    Next
  End With
  Unload RemoveCustomer
  Range("A5").Select
End Sub

 

[ipbr21054]

Maybe this is the code you require.

Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("GRASS").Range("B" & Rows.Count).End(xlUp).Row
  If lr > 4 Then ListBox1.RowSource = "GRASS!A5:B" & lr
End Sub

 

[Joe4]

As long as you always have at least one blank line in between your data and notes, try changing this line:

  lr = Sheets("GRASS").Range("B" & Rows.Count).End(xlUp).Row

to this:

  lr = Sheets("GRASS").Range("B5").End(xlDown).Row

 

[ipbr21054]

Great many thanmks.
I did notice if adding a note on the next line it would become part of the table but couldnt work out the code to start from top as oppose bottom.

All sorted now

 

[Joe4]

You are welcome.
Glad I was able to help.