Excel VBA CountA method on named range

Question

I've built a name range for 20 cells so that I can input a new list of projects which will vary from 1 to 20. I want to write a macro so that it reads the number of projects and creates the correct number of tabs, and names the tab after the project name listed in the named range. I've done all of this except I can't get the countA function to work. The named range is csCount. if I change the For loop to the correct number in one instance (if I put 7 because right now I have 7 projects) the loop and macro are correct. I want to make it more dynamic using the countA. Thank you very much for the help.

Sub generateDepartments()

Dim tabs As Integer
Dim sName As String
Dim i As Integer
Dim j As Integer
Dim csCount As Variant




tabs = Application.CountA(csCount)

j = 5
i = tabs



For i = 2 To Application.CountA(csCount)

    Worksheets("Input").Activate
    sName = Cells(j, 3).Value
    Worksheets.Add(after:=Worksheets(i)).Name = sName

    j = j + 1

Next


End Sub

Lucas Lucas · Accepted Answer · 2015-09-11T17:51:56

First you need to create a variable to access your named range: Set csCount = ActiveWorkbook.Names("csCount").RefersToRange or Set csCount = ActiveSheet.Range("csCount")

Then use Application.WorksheetFunction.CountA(csCount)

Also, is better to define as a Range instead of Variant Dim csCount As Range

Excel VBA CountA method on named range

1 Answers