Named ranges

[ogo]

Is there any way to create named ranges with VBA using row& column indexes, say create a named range where start row number us stored in myRow and end row number in endRow and similarly for columns myCol and endCol?

 

[AlphaFrog]

Try something like this...

ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Sheet1!" & Range(Cells(myRow, myCol), Cells(endRow, endCol)).Address

 

[ogo]

There is a change , the start and end columns are the same.

 

[ogo]

What exactly i need is that i have a column of data(some are repeating) whose start and end row value is stored in variables , now i want to add this data to drop down using named range and also i need to avoid duplicate entries. The length of data exceeds 255 characters so i must use named range..Can anyone help??

 

[AlphaFrog]

Use the Advanced Filter feature to filter your column for just the Unique entries. Copy those unique values from the filtered column to an empty column. The link gives you some very good example code to do that.

Use the new column of unique entries as the reference in your data validation drop down list. Or redefine your named range to the column of unique entries.