Range to be automatic as opposed to fixed

[ipbr21054]

Hi,

How can i change this existing working code so it isnt a fixed range.
I get easily confused each time with the xlup code for which it needs,sorry

Private Sub UserForm_Initialize()
Set ws = ActiveSheet
Me.TextBox1 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "HONDA MC KEY #1")
Me.TextBox2 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "HONDA MC KEY #2")
Me.TextBox3 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "HONDA MC KEY #3")
Me.TextBox4 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "HONDA MC KEY #4")
Me.TextBox5 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "REMOTE FOB 3B UK")
Me.TextBox6 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "YAMAHA YH35")
Me.TextBox7 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "BUNDLE")
Me.TextBox8 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "BLACK")
Me.TextBox9 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "GREY")
Me.TextBox10 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "RED")
Me.TextBox11 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "CLEAR")
Me.TextBox13 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$200"), "REMOTE FOB 3B USA")

Me.CommandButton1.SetFocus

End Sub

 

[Joe4]

Here is one way:

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim lr As Long
Dim rng As Range

Set ws = ActiveSheet

'Find last row with data on column K of sheet
lr = ws.Range("K" & Rows.Count).End(xlUp).Row

'Set range variable
Set rng = ws.Range("A8:K" & lr)

Me.TextBox1 = Application.WorksheetFunction.CountIf(rng, "HONDA MC KEY #1")
Me.TextBox2 = Application.WorksheetFunction.CountIf(rng, "HONDA MC KEY #2")
Me.TextBox3 = Application.WorksheetFunction.CountIf(rng, "HONDA MC KEY #3")
Me.TextBox4 = Application.WorksheetFunction.CountIf(rng, "HONDA MC KEY #4")
Me.TextBox5 = Application.WorksheetFunction.CountIf(rng, "REMOTE FOB 3B UK")
Me.TextBox6 = Application.WorksheetFunction.CountIf(rng, "YAMAHA YH35")
Me.TextBox7 = Application.WorksheetFunction.CountIf(rng, "BUNDLE")
Me.TextBox8 = Application.WorksheetFunction.CountIf(rng, "BLACK")
Me.TextBox9 = Application.WorksheetFunction.CountIf(rng, "GREY")
Me.TextBox10 = Application.WorksheetFunction.CountIf(rng, "RED")
Me.TextBox11 = Application.WorksheetFunction.CountIf(rng, "CLEAR")
Me.TextBox13 = Application.WorksheetFunction.CountIf(rng, "REMOTE FOB 3B USA")

Me.CommandButton1.SetFocus

End Sub

 

[ipbr21054]

Thanks very much.
Now posted another question.

Thanks