CODEWITHSUNDEEP
excelvba
EdinsonC
EdinsonC

Reputation: 19

How to pick out cells that are not empty within a range?

Function ln(sheet)
    ' this function locates the last names in a sheet
    ' and returns the range that they are contained in
    
    Dim sr As Range     ' search range
    Dim rc As Range     ' reference cell
    Dim nc As Range     ' name cell
    Dim nr As Range     ' name range
    Dim r As Range
    Dim objXl
    
    Set objXl = CreateObject("Excel.Application")
    
    If sheet = "main" Then
        Set sr = Sheet1.Range("A1:AZ80")
        Set rc = sr.Find(what:="1")
        Set nc = rc.Offset(0, 1)
        Set r = Application.Union(nc, nc.Offset(70, 0))
        Set nr = Worksheets("Sheet1").r.Cells.SpecialCells(xlCellTypeConstants).Count

The function is trying to find a cell within a column, where the first last name can be found. After finding the last name, the script is trying to create a range that will encompass all of the last names in that column, excluding any empty cells, hence the .Count in the last line.

I get the error

Run-Time Error 9: Subscript out of range

The last line is highlighted.

Upvotes: 0

Views: 41

Answers (1)

EdinsonC
EdinsonC

Reputation: 19

The answer: To remove the error: Set nr = r.SpecialCells(xlCellTypeConstants)

Thank you user3598756

Upvotes: 1

Related Questions