defined name

S

Scott

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
 
G

Gary''s Student

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.
 
G

Gary''s Student

let's make sure that CI1Total contains no surprises:

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

Gary''s Student

Got it.

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

nFirstRow = Range("CI1Total").Row
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top