Solved

VBA '1004' code error

Posted on 2014-04-03
8
757 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
Comment Utility
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
Comment Utility
Have you tried:
With ThisWorkbook.Names(FirstCellName).Range

Open in new window

0
 

Author Comment

by:discogs
Comment Utility
Uploaded file to assist Experts..
Book1.xlsx
0
 

Author Comment

by:discogs
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
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
Comment Utility
Absolute legend!! Mark, I knew it was something simple!!
0
 

Author Comment

by:discogs
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now