discogs
asked on
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:
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
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 FunctionI am getting an error ON LINE 3 ('1004' - "application-defined or object-defined error")
Any ideas on how I can amend this please?
TA
Have you tried:
With ThisWorkbook.Names(FirstCellName).RangeASKER
Uploaded file to assist Experts..
Book1.xlsx
Book1.xlsx
ASKER
Hello Mark.
Yes I have. It still provides the same error.
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 FunctionASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolute legend!! Mark, I knew it was something simple!!
ASKER
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
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
ASKER
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