Solved

VBA '1004' code error

Posted on 2014-04-03
8
765 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:discogs
  • 5
  • 3
8 Comments
 

Author Comment

by:discogs
ID: 39976988
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39977011
Have you tried:
With ThisWorkbook.Names(FirstCellName).Range

Open in new window

0
 

Author Comment

by:discogs
ID: 39977017
Uploaded file to assist Experts..
Book1.xlsx
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:discogs
ID: 39977023
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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39977078
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
 

Author Comment

by:discogs
ID: 39977085
Absolute legend!! Mark, I knew it was something simple!!
0
 

Author Comment

by:discogs
ID: 39977087
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39977288
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question