Dynamically assigning a name to cells

JCScoobyRS

Board Regular
Joined
Sep 17, 2002
Messages
102
I am pulling data from an Oracle Database and it's inserted into column AK. Once inserted, I'd like to create a drop down menu for the items just inserted, minus the first cell. The only problem is that the number of cells can increase or decrease depending on the database. Is there a way to automatically assign a name to the newly inserted cells? Reason being is that I want to create a spreadsheet for end users so that they don't have to do any formating themselves. If I create a cell to be a validation list and I can give a name to the newly inserted cells, the validation list would work no matter how many cells were inserted as long as the source for my list is looking for the name of the newly inserted cells. Thanks, Jeremy
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

davers5

Active Member
Joined
Feb 20, 2002
Messages
255
In your previous posting, you had some code that put the oracle stuff into excel. I think there is a place in the code that had this loop:

'Display Data
For Rownum = 2 To EmpDynaset.RecordCount + 1
For Colnum = 0 To fldcount - 1
ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
Next
EmpDynaset.DbMoveNext
Next

So, from this I think you can say that the total number of records (number of rows you need) is empdynaset.recordcount and the number of columns is fldcount. If you know that you can then name the range of cells that you'd like by saying something like:

Range(cells(1,1),cells(empdynaset.recordcount,1)).name="MyRange"

Dave
 

Forum statistics

Threads
1,144,766
Messages
5,726,167
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top