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