Insert…Name…Define

Topic

New Reply

I have a range called “database” in an Excel workbook that is eventually used to create a .dbf file for import into a FoxPro program. “Database” contains numbers and text and is always nine columns wide. However, because it is a combination of productivity results sent to me from the field, the number of rows sometimes fluctuates, usually about 560-580 rows.

I use a macro to insert the data into the Excel worksheet. First I select and delete the current data, then go to Insert…Name…Define and delete the name “database.” I then paste in the new data, and finally want to select the new range (e.g. 9 columns x the number of rows) and rename that as “database.”

To do that, I have the macro go to cell A1, then either 1) “Select Current Region” or 2) do a Shift-End-Down Arrow and a Shift-End-Right arrow to select the range of cells containing values, which is then named “database.” Eventually “database” becomes a .dbf file.

Here’s the problem: Both items 1 and 2 in the previous paragraph select the former range instead of the new one. For example, last week my range contained 574 rows. This week, it only has 560 rows. However, the new “database” still contains 574 rows, with the last 14 rows blank. It would be no big deal, except the Foxpro program displays an Error when it tries to import the .dbf file, and I have to go back to Excel and manually name the “database.”

Any ideas for fixing the macro? (Note I cannot alter the Foxpro program, so it needs to be done in Excel.)

Thanks!

Reply | Quote

Replies

Hi, Rick. What you’re looking for is called xldown. You’ll find some instructions at Microsoft Technet Article. Good luck!

Reply | Quote

Thanks, it worked! I used this code:

ActiveWorkbook.Names(“Database”).Delete
lastCol = ActiveSheet.Range(“a1”).End(xlToRight).Column
lastRow = ActiveSheet.Cells(16384, lastCol).EndxlUp).Row
ActiveSheet.Range(“a1”, ActiveSheet.Cells(lastRow, _ lastCol)).Select

This allows me to select my new range.

However, now I have an additional problem. Once I make the selection, I want to name it “database.” When I record a macro to name it database, it refers to the cell range: ActiveWorkbook.Names.Add Name:=”database”, RefersToR1C1:=”=dedb_in!R1C1:R580C9″

Then, when I run the macro the next time, it makes the same range (R1C1:R580C9) the “database”, rather than the selection I just made.

Is there some way to name the new selection “database” each time without having the cells from the previous database become the new database? For example, I tried:

ActiveWorkbook.Names.Add Name:=”database”, RefersToR1C1:= _
“=dedb_in!R1C1:(lastRow, lastCol)”

but it didn’t work (even though I didn’t get an error).

I’m pretty new to VBA, so any help is appreciated!

Reply | Quote

Rick,

Would something like:
ActiveWorkbook.Names.Add Name:=”database”, RefersToR1C1:= _
“=dedb_in!R1C1:R” & lastRow &”C” & lastCol”

do what you want?

Geoff

Reply | Quote

Time for me to come clean, Rick. Often, when I don’t know WHERE to look for help, I just type it into Yahoo. For your first question, I did know that it was xldown (and, as you found, xlup, etc.), so I put that in a search in Yahoo. The first return was the link I sent you. This time I typed in ActiveWorkbook.Names.Add Name and got another Technet link: http://msdn.microsoft.com/library/officede…ddNamesObjX.htm that appears to answer your question. (But I’m not sure!) You can always post these questions in the VB/VBA forum. Just remind them your a newbie to VBA (as am I).

Reply | Quote

You might also look at:

http://www.beyondtechnology.com/geeks007.shtml

a site run by Rodney Powell. This particular page discusses the creation of dynamic named ranges, which will expand and contract based on the number of non-blank lines in your s/sheet.

There are come limitations (as always) that may mean it won’t work for you – primarily that the database has to be the only thing on the sheet, and that in order to reference that named range the s/sheet has to be open (although not active).

I am not sure if Access (or was it FoxPro?) would deal properly with such a named range, but it might be worth a try.

Reply | Quote