Counting entries from dynamic range

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
12
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!

Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't your code work?
 

Anthropologista

New Member
Joined
Nov 5, 2017
Messages
12
Fluff,
I get error 1004: Application-defined or object-defined error.

On the line that reads
Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
That line should be
Code:
CurrentList = Application.WorksheetFunction.CountA(Sheets("TOL Categories").Range("B4", Target))
But I think that what you need is
Code:
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

New Member
Joined
Nov 5, 2017
Messages
12
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top