Concatenation Issue in Excel Macro 1

[GuyHamasaki]

I need to define an Excel Name in a macro. The Name is created in the macro as follows: ActiveWorkbook.Names.Add Name:="Test", RefersToR1C1:="=Test!R1C1:R1567C66"

I have created 2 string variables--one to capture the number of columns and the other to capture the number rows. I set these variables as follows.

strColumns = Range("A1&quot.End(xlToRight).Column
strRows = Range("A1&quot.End(xlDown).Row

How do I insert strRows where 1567 is and strColumns where 66 is?

I would also like to set ActiveWorkbook.Names.Add Name:="Test", RefersToR1C1:="=Test!R1C1:R1567C66" equal to another string variable, let's call it str1 for this example. How can this be done?

Thanks in advance,

Guy

 

[euskadi]

recommened you change strColumns and strRows to long integer:

dim lngColumns as long
dim lngRows as long


then this should do it:

str1 = "=Test!R1C1:R" & lngRows & "C" & lngColumns

 

[GuyHamasaki]

euskadi...thanks for the help. given your example above, i need to execute str1. what is the best way for this to happen.

thanks in advance,

guy

 

[MRussell]

Hello. If I understood your process correctly -- I have another idea on inserting the row and column at the end of the active range. (This works in Excel 2000 - lower levels might need a tweak)
Range("A1&quot.Select
Range(Selection, ActiveCell.SpecialCells _(xlLastCell)).Select
'looks like that is the cell you were trying to locate
ActiveCell.Offset(1, 1).Select
'this offsets the active cell one down and right
With Selection
.EntireRow.Insert
.EntireColumn.Insert
End With

Before you offset the activecell, you could add the range name to the selected range IF you just wanted to range nome that single cell then select the active cell and add the name then offset

ActiveWorkbook.Names.Add Name:="Test", _ RefersToR1C1:=Selection