Counting entries from dynamic range

[Anthropologista]

Hello all,
I am writing a simple macro to count the number of categories in a dynamic range by:

1. Offsetting the bottom of the range (“SectionInfant”) by -1
2. CountA the resulting range (B4 to Target)
3. Paste the resulting number in F1

I can’t get the following code to work. Any help is appreciated!

Sub Auto_Open()
 
Dim CurrentList As Integer
Dim Target As Range
 
Set Target = Range("SectionInfant").Offset(-1, 0)
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range(Range("B4", Target)))
Sheets("TOL Categories").Range("F1").Value = CurrentList
 
End Sub

 

[Fluff]

In what way doesn't your code work?

 

[Anthropologista]

Fluff,
I get error 1004: Application-defined or object-defined error.

On the line that reads

CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range(Range("B4", Target)))

CurrentList = 0

Also forgot to mention that "SectionInfant" is a defined range in my spreadsheet.

 

[Fluff]

That line should be

CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range("B4", Target))

But I think that what you need is

Dim CurrentList As Long
Dim Cl As Range
 
With Range("SectionInfant")
   Set Cl = .Offset(.Rows.Count - 2).Resize(1, 1)
End With
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range("B4", Cl))
Sheets("TOL Categories").Range("F1").Value = CurrentList

Also it's best to avoid using VBA keywords such as Target for variables.

 

[Anthropologista]

Fluff,
Thank you for your suggested code. It worked! I use VBA infrequently, so I very much appreciate the help provided by you and the other users.

 

[Fluff]

Glad to help & thanks for the feedback