defined name

[Guest]

How do I get the row number in the defined name "CI1Total" below?

Sub UpdateRows()
Workbooks.Open Filename:= "X1.xls"
Workbooks.Open Filename:= "X2.xls"
Windows("X1.xls").Activate
Sheets("X1-1").Select
RowCnt = CI1Total - 19 -- this causes an error
Windows("X2.xls").Activate
Sheets("X2-2").Select
InsertRows (RowCnt)
End Sub

Sub InsertRows(Rows)
For c = 1 To Rows
InsertARow (CI2Total)
Next
End Sub

Sub InsertARow(InputCI)
Range(InputCI).Offset(-2, 0).Select
Selection.Copy
Range(InputCI).Offset(-1, 0).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

 

[Guest]

If CI1Total is a range then:
nLastRow = CI1Total.Rows.Count + CI1Total.Row - 1
nFirstRow = CI1Total.Row
numrow = CI1Total.Rows.Count

are the first row, last row, and number of rows in the range.

 

[Guest]

The FirstRow example still kicks in the debugger. Runtime error 424.

 

[Guest]

let's make sure that CI1Total contains no surprises:

1. verify it is a range
2. verify it is visible within the macro

 

[Guest]

Got it.

The name of a Named Range is just a string. Try:

nFirstRow = Range("CI1Total").Row

 

[Guest]

Thanks. This worked.