Solved

VBA '1004' code error

Posted on 2014-04-03
8
761 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 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

19 Experts available now in Live!

Get 1:1 Help Now