defined name
 

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

defined name
 

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.

--
Gary''s Student


"Scott" wrote:

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

defined name
 

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

"Gary''s Student" wrote:

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.

--
Gary''s Student


"Scott" wrote:

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

defined name
 

let's make sure that CI1Total contains no surprises:

1. verify it is a range
2. verify it is visible within the macro
--
Gary's Student


"Scott" wrote:

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

"Gary''s Student" wrote:

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.

--
Gary''s Student


"Scott" wrote:

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

defined name
 

Got it.

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

nFirstRow = Range("CI1Total").Row
--
Gary''s Student


"Gary''s Student" wrote:

let's make sure that CI1Total contains no surprises:

1. verify it is a range
2. verify it is visible within the macro
--
Gary's Student


"Scott" wrote:

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

"Gary''s Student" wrote:

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.

--
Gary''s Student


"Scott" wrote:

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