Set Named Range with Last Row

I'm trying to create a Named Range called "DataRange" based on my values as 
listed in LISTING 1. For example, "DataRange" would currently be B2:B10. The 
trick to my problem is I will be inserting a row after Row 1 with code, thus 
shifting "DataRange" to B3:B11 for example. The other issue is I need the 
Named Range starting at B2 to the last row in Column B where data exists in 
Column A. To clarify, in LISTING 1 there is no data in B10, but there is 
data in A10. I will always need to get the last data row in Column A and 
then create the Named Range from B2:B10 for my example numbers below.

I found a code example shown in LISTING 2 below that creates a Named Range, 
but it has problems dealing with blanks.

Can someone shed some light on how I can accomplish this?


LISTING 1:

Column A    Column B
------------------------------
BOL           BOL Billed
196618       196650
196625
196650
196663       196663
196669
196686       196686
196694       196694
196699       196699
196711

LISTING 2:

Sub setNamedRange()

Dim lCol As Long, lRow As Long, rStart As Range
Dim rng As Range, ws As Worksheet

    Set ws = ActiveSheet
    With ws
        Set rStart = .Range("B2")
        lRow = rStart.End(xlDown).Row - 1
        lCol = rStart.End(xlToRight).Column
        Set rng = .Range(rStart, .Cells(lRow, lCol))
        .Names.Add Name:="DataRange", RefersTo:=rng
        .Range("DataRange").Select
    End With
End Sub 


0
scott
12/11/2007 5:58:45 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1021 Views

Similar Articles

[PageSpeed] 8

Sorry, accidently posted previous message in the wrong forum.

"scott" <sbailey@mileslumber.com> wrote in message 
news:uOcJJ$BPIHA.292@TK2MSFTNGP02.phx.gbl...
> I'm trying to create a Named Range called "DataRange" based on my values 
> as listed in LISTING 1. For example, "DataRange" would currently be 
> B2:B10. The trick to my problem is I will be inserting a row after Row 1 
> with code, thus shifting "DataRange" to B3:B11 for example. The other 
> issue is I need the Named Range starting at B2 to the last row in Column B 
> where data exists in Column A. To clarify, in LISTING 1 there is no data 
> in B10, but there is data in A10. I will always need to get the last data 
> row in Column A and then create the Named Range from B2:B10 for my example 
> numbers below.
>
> I found a code example shown in LISTING 2 below that creates a Named 
> Range, but it has problems dealing with blanks.
>
> Can someone shed some light on how I can accomplish this?
>
>
> LISTING 1:
>
> Column A    Column B
> ------------------------------
> BOL           BOL Billed
> 196618       196650
> 196625
> 196650
> 196663       196663
> 196669
> 196686       196686
> 196694       196694
> 196699       196699
> 196711
>
> LISTING 2:
>
> Sub setNamedRange()
>
> Dim lCol As Long, lRow As Long, rStart As Range
> Dim rng As Range, ws As Worksheet
>
>    Set ws = ActiveSheet
>    With ws
>        Set rStart = .Range("B2")
>        lRow = rStart.End(xlDown).Row - 1
>        lCol = rStart.End(xlToRight).Column
>        Set rng = .Range(rStart, .Cells(lRow, lCol))
>        .Names.Add Name:="DataRange", RefersTo:=rng
>        .Range("DataRange").Select
>    End With
> End Sub
> 


0
scott
12/11/2007 6:38:36 PM
Reply:

Similar Artilces:

changing report headings names
I would like to give the user the option of changing the report heading names, I can do this with the Docmd.openreport, stdocname, acviewdesign,,,achidden and then grab the current heading and display that heading and allow the user to change it. I then do a docmd.close acreport, stdocname, acSaveYes, this works just fine when I test but when I compile the program into a mde it doesn't like the it. Is there another way of doing this? Thanks for any help. Tom you can write the current heading names to a table, making sure the table holds only one record. add the table to the quer...

Long Description? Manufacturer name? Images?
Hi, We ahve GP 8.0 and want to add an additional long description to items (short paragraph). Is there a field the either is created for this or could be used for this purpose? Same thing with Manufacturer. We want to be able to store this information in it's own field attached to the item. And anywhere for images? Thanks, Jason ------=_NextPart_0001_8B600AF1 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Jason, This is Angela; I'd be happy to help you with your question today. Thank you for using newsgroups. It looks like you want to add a long description (...

Charting
My chart shows temperatures in fresh water. The Y-axis is turned to show the depth of the lake by double- clicking the scale. In this dialog box I can put in the maximum and minimum values and a value to show how the depth should increase. The X-axis should visualize the temperatures being lower according to deeper water. (As a plan B I can refer to a range of cells representing the temperatures. This works.) It is possible to turn on an option so that it is possible to alter values on the X-axis the same way as on the Y-axis. How do I turn on that option? You don't mention ...

Select range in macro
Hi all. I have found a macro that will email the active worksheet. On the worksheet I have 2 command buttons and some instructions for the operater so they know where to input info. How can I select a range so that only a specific area will be sent. Below is the macro and the area I want to send is ("A1:J69"). Your help would be of great help. Regards Chris Public Sub Email_Midnight_Report() Dim wb As Workbook Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Email_ " &...

Exchange 5.5 Client setup
Hi We have an old Exchange 5.5 server on NT4 (all SP and relevant patches). Now when we connect up a new client at the point Outlook (2000 or 2003) checks for the user/server it says name not matched . The accounts all work when an admin account on the Server domain is used ie finds user etc ok. Also existing profiles have been getting odd errors when sending to external email addreses. Normally related to permissions denial or mapi encounterted an error please retry or restart Outlook. Recent changes are: A user accessing with XP SP2 on Outlook 2003, had problems authenticating and t...

Columns following the Row in an Imported Table
I have imported some data into Excel using MS-Query and an ODBC connection to an AS/400 and then added a column to the table that allows the entry of text, Example Column "A" is Customer # (From Database), Column "B" is Customer Name (From Data Base), Column "C" is a column I added to the table to allow the entry of a Ranking Code. The problem is that after entering the Ranking codes they will not follow the rows when the data is "Refreshed" Example if I enter a ranking on "C3" and the data in "A3 and B3" get moved dow...

Deleting rows in filter mode
I recently upgraded to Excel 2007 and tried to delete rows in filter mode like I did with Excel 2003 but the spreasheet didn't refresh without the rows I deleted. It was convenient with 2003 because once I autofiltered again it had my deleted rows gone from the worksheet. Any suggestion? ...

can no longer download T Rowe Price statements from within Money
Keep getting the following error message: The Internet, banking, or brokerage server could not process the request. Please try your call again later. (OFXHTTP400) Tried to go back into Account Setup, set up online services, and can't connect to TRP from there either. I've been unable to connect for the past 3 weeks, but all my other accounts work fine. Anyone else getting this error, or know a fix for it? Oh, I'm running Money 2004 Deluxe. I download from T. Rowe Price just fine, but that's in 2005. You may want to log on to T.Rowe Price website using the same credent...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Set Number of Previous Documents under FILE
When I open Excel and click on FILE on the menu bar, it only shows 4 previously opened documents. I want it to show 8-10 but can't find any options to change it. Can it be done? I have the student and teacher edition 2003. <Tools> <Options> <General> tab, Change number of entries by the "RecentlyUsedFileList". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Carolyn" <anonymous@discussions.microsoft.com> wrote i...

Outlook 2000 display name
Trying to change the display name of an account in Outlook 2000 SP-3 Windows XP machine But I can't find the option to do it, all I keep on getting is e-mail services and nothing that says accounts even when I go through options. Any help would be appreciated Corp/Workgroup mode does not use the term account. Your Internet Mail Service is your email account. Set the name there in Properties -- Russ Valentine [MVP-Outlook] "Miranda" <anonymous@discussions.microsoft.com> wrote in message news:0c2a01c46e89$a46fc5e0$a601280a@phx.gbl... > Trying to change the display n...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

Display names in Global address book do not update
I made a change to one of my users so that their display name shows as last name, first name. This change has not been applied to the global address book, but you can see the change has been made in active directory users and computers. Any idea how I update this change for the global address book? Thanks, Mike YOu need to wait for the global catalogs to refresh.....depends on your AD topology. "Mike Fefferman" <mike@nowhere.com> wrote in message news:%23v%23sxI5PFHA.3156@TK2MSFTNGP15.phx.gbl... >I made a change to one of my users so that their display name show...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

The sheet path file name ?
Hi all , How can i edit in a cell a formula to retrieve the whole path of th sheet and not only the file ! Thank you veru much -- gaftali ----------------------------------------------------------------------- gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=645 View this thread: http://www.excelforum.com/showthread.php?threadid=48453 If you can use a UDF in your workbook then you could use one like so Function FullPath() As String Application.Volatile True FullPath = ThisWorkbook.FullName End Function -- HTH Nick Hodge Microsoft MVP - Excel Sout...

Showing Duplicate Rows
Hi All, I have seen the question with that same title however that is not exactly "showing" duplicates. It is more showing unique (not duplicate) I would like to truly show the duplicates. For instance there are several duplicate product numbers that are in my sheet and I want to display all the duplicates so that I can make corrections. IE: Item number 31323 31324 31325 31326 31326 31327 31328 31328 31329 31330 31331 How can I filter so that excel will only show those rows that have duplicate Item numbers? Thanks Eagle Use data>filter>autofilter>filter by column...

'07 Large Operation Error (Delete / Insert Rows and/or Columns)
Help! I was so excited to have '07 until... I have a very simple worksheet in a work book - This very simple worksheet takes up 78 rows and 16 columns. Minimals formats / formulas etc. I would like to insert additional rows but I get the error message which says "Large Operation. The operation you are about to perform affects a large # of cells and may take a significant amount of time to complete. Are you sure you want to continue? Note: This operation will be performed in 60 seconds if no response is received". I have tried to select "OK" as well a...

Can I set a specific account for sending a message ?
In my outlook 2003 set up I have three internet e-mail accounts, used for different purposes. I have three different macros which I use to forward emails to different addresses, but I want each macro to use a specific e-mail account. Is it possible when using automation to send messages in visual basic to programmatically set a specific account as the account to be used when sending the message. I know that TO, SUBJECT, CC, ADDRESS, and so on can be set, but I haven't been able to find any visual basic code to cover setting a specific account. Outlook 2007 adds a...

increase total rows
Hi, can someone show me how to increase the maximum number of rows, more than 65,536? thanks Stan I presume you have Excel 2007? Open your .xls file in Excel 2007, save the file as an Excel 2007 file type (.xlsw, .xlsx, etc) using File Save As. Close the file and reopen it you should now have 2007's 1M+ rows available. "Stan" wrote: > Hi, > can someone show me how to increase the maximum number of rows, more than > 65,536? > thanks > Stan Thanks!!! "JLatham" wrote: > I presume you have Excel 2007? > > Open ...

Exchange 2003 SP2 IMF set to archive but is deleting e-mails?!
I have enabled IMF on Exchange Server 2003 SP2, and setup the following: "Gateway Blocking Configuration" is set to "7" and to "Archive" "Store Junk E-mail Configuration" is set to "7" thereafter I restarted the Information Store + SMTP Virtual Server. Spam e-mails gets archived very nice, but after af few days, to my horror, I now see alot of "1039" entries in the Messaging tracking log, which means "SMTP message deleted by Intelligent Message Filtering"??! (according to: Message tracking event IDs in Exchange Server...

Need to set up ID on Outlook Express
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=56dc2f64-f688-4919-a5ea-23b1a55eb054&dg=microsoft.public.outlook Try posting this in an Outlook Express news group - this is not one of them. Outlook i...

How to set up two accounts with one brokerage?
I am using MS Money 2003. I have two accounts with one online brokerage. One is my 401k and one is just some stocks. In order to log in, I have to use a different user name and password depending which account I want to access. The problem is, MS Money only allows me to enter one user name and password for each brokerage. I change the online properties of each MS Money account one at a time, but the changes do not stick. Both accounts wind up with the user name and password of the account I changed last? Am I doing something wrong? Any help would be appreciated. In microsoft...

Email Archiving/Routing to Public Folders, based on domain names
Ok, here's what I'm going for: I'm beginning to use Sharepoint to manage my client's network configurations (documentation, knowledgebases, etc). I plan on having a unique Sharepoint site per client, and I'm well aware of how to link a public folder to the site. What I'd like to accomplish - to have all email sent and received to a specific client journaled/archived to a public folder, which is linked to that particular client's sharepoint site. Eg - joe@contoso.com emails me with his IT issue. I respond back, resolve Joe's problem, everything's ...

email by rows
hello All: I'm looking for a macro that will go down a sheet and look for th email address and mail every thing in that row to *ONLY* that person n matter if it has 10 columns or 20 columns I have a sheet set up lik this Employee Name LAB Degree Section E-Mail Address paper1 paper2 paper3 I will be adding more columns as the year goes on that is why it nee to be able to send different size rows. Any help would be appreciated Dal -- Message posted from http://www.ExcelForum.com Hi DaleL Which mail program do you use -- Regards Ron de Bruin http://www.rondebruin.nl "D...

Last Synchronization Time
Is there a way of finding out, from the server, when a user last successfully synchronized the Outlook Client (went offline)? Maybe in a log or somewhere in a table? MS CRM 3.0 Thanks, Chris ...