News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Insert…Name…Define

    Posted on WSRickB Comment on the AskWoody Lounge
    Viewing 1 reply thread
    • Author
      Posts
      • #351479 Reply
        WSRickB
        AskWoody Lounger


        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!

      • #508603 Reply
        WSDreamboat
        AskWoody Lounger

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

        • #508617 Reply
          WSRickB
          AskWoody Lounger

          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!

          • #508619 Reply
            WSgwhitfield
            AskWoody Lounger

            Rick,

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

            do what you want?

            Geoff

            • #508622 Reply
              WSDreamboat
              AskWoody Lounger

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

              • #508693 Reply
                WSdcardno
                AskWoody Lounger

                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.

    Viewing 1 reply thread

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Insert…Name…Define

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.