VBA '1004' code error

Hi all.

Using excel 2010, the following code is intended to return the row count of a range starting at the cell with the named range:

Function ReturnRangeRowCount(ByVal FirstCellName As String) As Long

    With ThisWorkbook.Names(FirstCellName).RefersToRange
        If IsEmpty(.Offset(1, 0).Value) Then
            ReturnRangeRowCount = 1
        Else
            ReturnRangeRowCount = .End(xlDown).Row - .Row + 1
        End If
    End With
End Function

Open in new window


I am getting an error ON LINE 3 ('1004' - "application-defined or object-defined error")

Any ideas on how I can amend this please?

TA
discogsAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Your original code was correct.  The correct property is RefersToRange

Try testing with a named range of "testdata1".  That is one of the named ranges in the workbook you posted.
0
 
discogsAuthor Commented:
Just for clarity.

My named range is TestData and consists of one cell only which is intended as a marker for my inbound and outbound data routines.

The purpose of this question is to allow me to return the total rows that are contained directly below my TestData range.

So for example, my named range resides in C36 and I have data directly below it that stops at C40. This means I would need to delete 4 rows. The number of rows of data below my rangename (TestData) can be anything from 1 to 100.

Hope this helps.
TA
0
 
aikimarkCommented:
Have you tried:
With ThisWorkbook.Names(FirstCellName).Range

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
discogsAuthor Commented:
Uploaded file to assist Experts..
Book1.xlsx
0
 
discogsAuthor Commented:
Hello Mark.

Yes I have. It still provides the same error.

Function ReturnRangeRowCount(ByVal FirstCellName As String) As Long
     'With ThisWorkbook.Names(FirstCellName).RefersToRange
    With ThisWorkbook.Names(FirstCellName).Range
        If IsEmpty(.Offset(1, 0).Value) Then
            ReturnRangeRowCount = 1
        Else
            ReturnRangeRowCount = .End(xlDown).Row - .Row + 1
        End If
    End With
End Function

Open in new window

0
 
discogsAuthor Commented:
Absolute legend!! Mark, I knew it was something simple!!
0
 
discogsAuthor Commented:
On the same topic, Mark, is it possible to do the same thing using a loop counter. I have been trying to get this to work as well to no avail. Can you help?

Public Function CountDataRows() As Long
    'Pass starting location in spreadsheet for function to loop through until
    'empty cell is found. Return total count of rows with data
Dim wks As worksheet
Dim wkb As Workbook
Dim rng As Range
Dim Counter As Long

Set wkb = ActiveWorkbook
Set wks = wkb.Sheets("Test")
Set rng = wks.Range("testrow")

    With wks
        Counter = 0
    
        Do Until wks.Range("testrow").Offset(Counter, 0) Is Not Empty
    
        Counter = Counter + 1
    
        Loop
    End With

CountDataRows = Counter

End Function

Open in new window

0
 
aikimarkCommented:
Probably can do it with iteration.  It would look something like this:
        Do Until Len(wks.Range("testrow").Offset(Counter, 0).Value) = 0
    
        Counter = Counter + 1
    
        Loop

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.