Solved

VBA '1004' code error

Posted on 2014-04-03
8
780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

718 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