Is there an easier way to address ranges??

Hi,

I've created the bit of code below to copy a row of data from 
worksheets to one worksheet so that the rows are appended to each othe
and then finally sorted.

The code works fine but seems to be rather cumbersome.  Is there a
easier way of addressing ranges rather than having to create/defin
them first?  Any advice of my use of the other fuctions would also b
greatly appreciated.

Thanks in advance,

Dan


Sub FindUniqueBSN()
'
' FindUniqueBSN Macro

Dim WS1Name As String
Dim WS2Name As String
Dim WS3Name As String

Sheets.Add.Name = "UniqueBSNList"
WS1Name = "All Open Tickets"
WS2Name = "Tickets raised this month"
WS3Name = "Tickets closed this month"

'Set up 3 ranges for each worksheet
ActiveWorkbook.Names.Add Name:="WS1BSN", RefersToR1C1:= _
"=OFFSET('" & WS1Name & "'!R4C2,0,0,COUNTA('" & WS1Name 
"'!C1)-2,1)"
ActiveWorkbook.Names.Add Name:="WS2BSN", RefersToR1C1:= _
"=OFFSET('" & WS2Name & "'!R4C2,0,0,COUNTA('" & WS2Name 
"'!C1)-2,1)"
ActiveWorkbook.Names.Add Name:="WS3BSN", RefersToR1C1:= _
"=OFFSET('" & WS3Name & "'!R4C2,0,0,COUNTA('" & WS3Name 
"'!C1)-2,1)"
'Copy 1st range to new worksheet
Sheets(WS1Name).Select
Range("WS1BSN").Select
Selection.Copy Sheets("UniqueBSNList").Range("B2")
'Copy 2nd range to new worksheet but at end of 1st range
Sheets(WS2Name).Select
Range("WS2BSN").Select
ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
"=OFFSET('UniqueBSNList'!R1C2,COUNTA('UniqueBSNList'!C2)+1,0)"
Selection.Copy Sheets("UniqueBSNList").Range("BSNLength")
'Copy 3rd range to new worksheet but at end of both previous ranges
Sheets(WS3Name).Select
Range("WS3BSN").Select
ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
"=OFFSET('UniqueBSNList'!R1C2,COUNTA('UniqueBSNList'!C2)+1,0)"
Selection.Copy Sheets("UniqueBSNList").Range("BSNLength")
'Select entire range then sort it
ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
"=OFFSET('UniqueBSNList'!R2C2,0,0,COUNTA('UniqueBSNList'!C2),1)"
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGues
_
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Su

--
Message posted from http://www.ExcelForum.com

0
12/22/2003 5:52:47 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
160 Views

Similar Articles

[PageSpeed] 24

I made some assumptions on how the ranges were laid out.  (I bet you had some
empty cells in B1:B4 of each sheet.)

The assumption I made was that B4 contained the first real BSN and it extended
all the way down the column.  (In fact, I used VBA's equivalent of going to the
bottom of column B (B65536, hitting End and then hitting the Up Arrow to find
that last used cell in that column.)

And since your routine is called FindUniqueBSN, I figured that you really wanted
a list of unique BSN with no duplicates.  After you have the list of all the
BSN's in a column, you can use Data|filter|Advanced filter to copy the unique
values to another range.  (That's what that last section does.  It also deleted
the long list that contained the duplicates.)



Option Explicit
Sub FindUniqueBSN1()

    Dim WSNames As Variant
    Dim iCtr As Long
    Dim myRng As Range
    Dim wks As Worksheet
    Dim uniqueWks As Worksheet
    Dim destCell As Range
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("uniquebsnList").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    Set uniqueWks = Worksheets.Add
    With uniqueWks
        .Name = "UniqueBSNList"
        .Range("a1").Value = "Uniques"
    End With
    
    WSNames = Array("All Open Tickets", _
                    "Tickets raised this month", _
                    "Tickets closed this month")
    
    'this does the copying
    For iCtr = LBound(WSNames) To UBound(WSNames)
        Set wks = Worksheets(WSNames(iCtr))
        
        With uniqueWks
            Set destCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
        End With
        
        With wks
            .Range("B4", .Cells(.Rows.Count, "B").End(xlUp)).Copy _
                Destination:=destCell
        End With
    Next iCtr
    
    'this does the "uniquing"
    With uniqueWks
        .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=.Range("b1"), Unique:=True
        .Range("a1").EntireColumn.Delete
        .Range("b1").EntireColumn.Sort key1:=.Range("b1"), Order1:=xlAscending,
_
            Header:=xlYes, OrderCustom:=1, MatchCase:=False
    End With
    
End Sub

dororke wrote:
> 
> Hi,
> 
> I've created the bit of code below to copy a row of data from 3
> worksheets to one worksheet so that the rows are appended to each other
> and then finally sorted.
> 
> The code works fine but seems to be rather cumbersome.  Is there an
> easier way of addressing ranges rather than having to create/define
> them first?  Any advice of my use of the other fuctions would also be
> greatly appreciated.
> 
> Thanks in advance,
> 
> Dan
> 
> Sub FindUniqueBSN()
> '
> ' FindUniqueBSN Macro
> 
> Dim WS1Name As String
> Dim WS2Name As String
> Dim WS3Name As String
> 
> Sheets.Add.Name = "UniqueBSNList"
> WS1Name = "All Open Tickets"
> WS2Name = "Tickets raised this month"
> WS3Name = "Tickets closed this month"
> 
> 'Set up 3 ranges for each worksheet
> ActiveWorkbook.Names.Add Name:="WS1BSN", RefersToR1C1:= _
> "=OFFSET('" & WS1Name & "'!R4C2,0,0,COUNTA('" & WS1Name &
> "'!C1)-2,1)"
> ActiveWorkbook.Names.Add Name:="WS2BSN", RefersToR1C1:= _
> "=OFFSET('" & WS2Name & "'!R4C2,0,0,COUNTA('" & WS2Name &
> "'!C1)-2,1)"
> ActiveWorkbook.Names.Add Name:="WS3BSN", RefersToR1C1:= _
> "=OFFSET('" & WS3Name & "'!R4C2,0,0,COUNTA('" & WS3Name &
> "'!C1)-2,1)"
> 'Copy 1st range to new worksheet
> Sheets(WS1Name).Select
> Range("WS1BSN").Select
> Selection.Copy Sheets("UniqueBSNList").Range("B2")
> 'Copy 2nd range to new worksheet but at end of 1st range
> Sheets(WS2Name).Select
> Range("WS2BSN").Select
> ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
> "=OFFSET('UniqueBSNList'!R1C2,COUNTA('UniqueBSNList'!C2)+1,0)"
> Selection.Copy Sheets("UniqueBSNList").Range("BSNLength")
> 'Copy 3rd range to new worksheet but at end of both previous ranges
> Sheets(WS3Name).Select
> Range("WS3BSN").Select
> ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
> "=OFFSET('UniqueBSNList'!R1C2,COUNTA('UniqueBSNList'!C2)+1,0)"
> Selection.Copy Sheets("UniqueBSNList").Range("BSNLength")
> 'Select entire range then sort it
> ActiveWorkbook.Names.Add Name:="BSNLength", RefersToR1C1:= _
> "=OFFSET('UniqueBSNList'!R2C2,0,0,COUNTA('UniqueBSNList'!C2),1)"
> Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess
> _
> , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> 
> End Sub
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/22/2003 3:42:18 PM
Reply:

Similar Artilces:

how can I use my e-mail addresses on excel xp with microsoft outlo
I need to use my addresses on Excel xp with Microsoft outlook. I want to use my excel file as a database that I can change and improve everyday. I need to send mails to more than 1 contact, so creating an e.mail link is not enough for me: in fact, if I click on it, I send my mail only to 1 address... Txs See http://www.rondebruin.nl/sendmail.htm -- HTH RP (remove nothere from the email address if mailing direct) "Cat" <Cat@discussions.microsoft.com> wrote in message news:C7A9A930-6846-4071-8714-ED9CC50CD79F@microsoft.com... > > I need to use my addresses on E...

Spam mail coming from my own Email address
Recently, I have been getting Spam E mail as if it were being sent by myself. I have set up rules that get rid of most of the junk, but have also set a rule to not block mail from personal contacts including my own address. With that rule, somehow I am getting spam mail as if i sent it to myself. Anyone know whats causing this and the cure?? (I had an earlier reply to this question questioning why I would send an E mail to myself. If that person read this again, it simple. I use my Email both at work and at home and very often will forward material to myself so I can retrieve i...

blocking internet addresses
How can I block certain sites and banners from showing up on my Outlook Express? Thanks in advance Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "steve" <anonymous...

Pasting email address into text field
Hello all, I am attempting to copy/paste several rows of data from an excel spreadsheet into an Access form. I receive an error: "The value you have entered into this field is invalid" when the data contains an email address. The email address field is set for text with a field length of 255 characters in the DB. I can copy the individual email address into the field without any problems. the problem occurs only when I am copying the whole row or rows of data from the excel spreadsheet into the Access DB. Any ideas about getting around this problem? Hi, this is exactly wh...

In CRM 3.0 is there a way to Hide the Sales or Maketing or Service
I want to be able to control users access to different sections of CRM. So I want Jane Smith to see Sales and Marketing, and Scott Riley to only see Sales and Service. Doing by team would also work. I was able to create custom Roles and control entiy access but I don't see a way of controlling section access. Mike Sections only appear if the user has access to one of the items within that section. Unfortunately, there are some shared objects across the sections (like Account/Contact) that prevent them from disappearing totally. -- Matt Parks MVP - Microsoft CRM "Hotwheels...

How do you select a cell with an "absolute" address with a cursor?
When you try to make an absolute reference to a cell, you place "$" in front of the column and/or row. But can you do it more easily by, for example, selecting a cell with your mouse while pressing CTRL (obviously this does not work) when you select a cell? Thank you. New Daddy, select the cell press F2 then F4 to toggle absolute -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "New Daddy" <newlywed2@daum.net>...

data range referrences
Hi, I have a chart which relies on the data in a table with 29 rows. Occaisionally, I want to only use 28 rows as my data set for the chart. I can manually enter a named range into the dialog, and upon entry, it automatically ,morphs it into the string that describes the data range (worksheetname, data set). When I manually type in the other named range, it flips to the shorter named range in the table. How can I inject that into the chart's data dialog on the fly? I have tried to reference cell locations that have the named range name in them. No worky. I know it is pos...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

can you print your address book with photos?
I want to print a directory for the members of a club and wanted to include a photograph, but I cannot figure out how to add the photo that I already have in their contact information into the booklet. No, the photo does not print. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Poll:...

How do I set up a URL address to send and recieve e mail?
I'm using Outlook 2003. How can I set Outlook up to send and recieve e mail from Microsoft Web Access? It has a https URL. -- Thank You James P Murphy Outlook's WebDav (HTTP) protocol doesn't support HTTPS urls. http://www.outlook-tips.net/archives/2005/20050322.htm -- "J Murphy" <JMurphy@discussions.microsoft.com> wrote in message news:0D0201CB-113B-44C6-946B-206C3AAF069D@microsoft.com... > I'm using Outlook 2003. How can I set Outlook up to send and recieve e > mail > from Microsoft Web Access? It has a https URL. > -- > Thank You...

Name Matching
Hi when an email is sent to a miss-spelled address, how do I route these emails to a postmaster or admin account? Thanks Matt What version of Exchange? -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Matt" <mattremoveme@edirect.co.uk> wrote in message news:006001c3b506$6c6670f0$a301280a@phx.gbl... > Hi > > when an email is sent to a miss-spelled address, how do I > route these emails to a postmaster or admin account? > > Thanks > Matt Exchange Server 2003 Running on Windows Server 2003 >----...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Test variable range for 'Delete'; then delete the row
I’m getting a run-time error 1004 here. Application defined or object defined error. The error seems to occur here: For Each c In Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("O12:O" & rngFound)) Private Sub CommandButton4_Click() Dim c As Range Dim rDelete As Range Dim rngFound As Range Dim rngToSearch As Range With Range("A:A") Set rngToSearch = Sheets("Worksheet").Columns("A") Set rngFound = rngToSearch.Find(What:="Enter non-listed privately held securities or groups of assets by asset class.", _ ...

Edit specific range in protected worksheet
Dear Sir/Mam, I have a workbook with multiple worksheets. I protected a sheet but some menu does not work when it is protectd. e.g. i want to go edit > fill > series. How it can be possible. Thanks in advance Rao Ratan Singh If it's protected, you simply can't. You'd have to unprotect it first. Or write a VBA procedure to unprotect, do the fill series, reprotect. Bob Umlas Excel MVP "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:FF6C347C-1BEE-40D3-ADE1-E617A7033F0D@microsoft.com... > Dear Sir/Mam, > I have a w...

How do I automatically update a chart range in Excel
I am trying to create charts that should automatically update. I have been trying to use the OFFSET function in the chart 'series value' to determine the chart range and this does not sem to be working. Does anyone have a suggestion with this problem. Hi, As far as I know, the chart is updated automatically when the data use in it is changed. Or did I miss something in your description? Best Regards Lub -- lshili ----------------------------------------------------------------------- lshilin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2338 View ...

Getting "program trying to access addresses" message when sending email
Whenever I open up a new "Compose Mail" window and start typing addresses into the "To" field, Outlook brings up the message that a program is trying to access email addresses I've stored in Outlook, and asking whether I want to allow it. Any reason this is happening even though the program that's trying to access those addresses is Outlook itself? Do you have the full version of Adobe Acrobat 6 installed? If yes, you might have to disable the PDFMaker addin. In any event, check out this section http://www.slipstick.com/outlook/ol2002sp3.htm#problems to s...

Better way to make a downpayment on an unreceived purchase order
So far, I know of two ways to make a downpayment on an unreceived purchase order. The first is to receive, either partially or in full, the order in order to 'create' an invoice. The other way is to create a manual check & apply it once the order is received and invoiced. Neither of these is ideal. Both options leave a poor audit/paper trail. ---------------- 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 bu...

Allowing Clients to Configure a Forwarding Address for a Public Folder
Hi We have a general 'enquiry' email address for our company where initial emails from clients will come to and our fax server sends desktop faxes to, this is a public folder on our server. So that faxes and emails are responded to immediately we have configured the enquiry public folder to also copy the email to the receptionist, they then get a popup message in Outlook when the email arrives. Recently our receptionist has taken on other roles and is often being covered by others for a few hours at a time, I'm getting fed up of logging into System Manager and changing thi...

How do I remove the popup email addresses that I don't need or may have errors.
How do I remove the popup email addresses that I don't need or may have errors. Example:- somobody@thisplace.com :error somebody@thisplace.com :OK highlight them and delete "Barry McConomy" <smile@vianet.ca> wrote in message news:103mp3hp7npqk02@corp.supernews.com... > How do I remove the popup email addresses that I don't need or may have > errors. > > Example:- > > somobody@thisplace.com :error > somebody@thisplace.com :OK > > Barry McConomy <smile@vianet.ca> wrote: > How do I remove the popup email addresses that I don'...

Address Book Sharing/Creation
We are attempting to get our Exchange server up and running and I am quite new to the whole Microsoft Mail thing... (long time sendmail/qmail user) We are attempting to share Address Books and enable the default address book on our exchange server. Currently the default AB that comes up is basically the listing of users on the exchange server. I would like to create a couple address books from current information. I have a saved CSV that contains our customer data that I would like to import. I have succesfully done this in my personal Outlook but when I attempt to share the AB other users are...

Lookup? Ranges?
First of all, I have to tell everyone who responds to post how WONDERFUL this is. I've gotten a LOT of information from you smarties. I have a workbook with several tabs. One tab has a series of tables for ordering merchandise by size (lengths across the top, in columns, widths within the rows). The grid within the column/row headers allows me to enter quantities by size (length and width). Each of the tables has it's own ID (not using the NAME function, just from typing in an ID). What I want to do on the second worksheet is be able to enter that ID and have the ordered units ...

Dynamic named ranges
I'm trying to import an Excel spreadsheet into an existing Access table using Office 2003. Ultimately, the plan is to do it programmatically using TransferSpreadsheet, but to check that the file has no problems, I've done it manually with the Import Spreadsheet wizard. The worksheet has 43 rows, and I import a named range defined as "=Sheet1!$C:$E". The import works, but I get a table with 64K rows, all but 43 being blank! Is there a way to define the range so that only filled rows will be imported? I don't want to use the last row's address explicitly, because I wi...

Any way to change default FIND options?
When I do a CTRL+F to find information, the OPTIONS always is set to Look In Formulas. Is there any way to get is to default to Look In Values? thanks. One way: Put this in the ThisWorkbook module of your Personal.xls workbook (or any other workbook in the XLStart directory): Private Sub Workbook_Open() Dim result As Range Set result = Sheets(1).Cells.Find( _ What:="", _ LookIn:=xlValues) End Sub Find options are persistent, whether done in the GUI or in VBA, so doing a find will set the LookIn value for the next search. In ...

Newbie: Auto fowarding to external email address not working
We have recently switched from using Exchange 5.5 to Exchange 2003. (We have a small office and responsibility for the IT falls on me, mostly I'm happy with this but I feel a little out of my depth with Exchange.) Our MD has a rule to forward a copy of all incoming email to his blackberry (basically forwarding it to an external email address). This worked fine when we were working with Exchange 5.5, but since migrating everyone to our new Exchange 2003 server this has stopped working. I know the rule fires, because if I add an internal address then that recipient gets it. I also kno...

Can't find how to change Y-axis range to show new data in Excel Mac 12.2.4
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a simple bar chart that I created some years ago in a previous version of Excel. I add data each month and it shows miles I drive per month over the year, very helpful to keep track of distance and gas mileage. I've extended the Y-axis range downward a few times before, but can't find out how to do it this time. I always install the latest updates from Microsoft and now I'm running Excel 2008 for Mac version 12.2.4 (100205). My Y-axis range in this chart starts at 800, but I drove only 772 miles ...