Named Ranges - Macro Problems

Hi!

I created several worksheets that contained huge lists, so I created a
dynamic named range.  Now that the name has been inserted in my array
formulas, my Macro runs very slowly.  The Macro unprotects one of my sheets,
sorts the data in a table, hides the blank rows and the protects the sheet
again.  There are no related named range formulas in that worksheet, so I'm
not sure why it would be affected.  The Macro ran quickly when I had a
defined range of data.

Would giving a named range like 'Date' and having text in my workbook with
'Date' create conflicts?  Could this affect my Macros?

I'll be more than happy to send the formulas and Macro if you want to look
at them further.

Thanks!


0
kfb1 (138)
4/3/2004 5:30:29 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
473 Views

Similar Articles

[PageSpeed] 58

Hi Karl,

Check that you have

Application.calculation=xlManual

.... your code

Application.calculation=xlautomatic


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Karl Burrows" <kfb1@spambellsouth.net> wrote in message
news:SXrbc.17144$xW1.3997@bignews4.bellsouth.net...
> Hi!
>
> I created several worksheets that contained huge lists, so I created a
> dynamic named range.  Now that the name has been inserted in my array
> formulas, my Macro runs very slowly.  The Macro unprotects one of my
sheets,
> sorts the data in a table, hides the blank rows and the protects the sheet
> again.  There are no related named range formulas in that worksheet, so
I'm
> not sure why it would be affected.  The Macro ran quickly when I had a
> defined range of data.
>
> Would giving a named range like 'Date' and having text in my workbook with
> 'Date' create conflicts?  Could this affect my Macros?
>
> I'll be more than happy to send the formulas and Macro if you want to look
> at them further.
>
> Thanks!
>
>


0
Charles740 (233)
4/3/2004 8:18:26 AM
I am going to attach the code.  I put this together from several smaller
snippets of code (I am not a VBA expert by any stretch of the imagination).
Are there any other suggestions to clean up my code to help it run smoother?

Thanks!

Sub UnhideSortHideRows()
ActiveSheet.Unprotect Password:="xxxx"
'
' UnhideRows Macro
'
    Rows("11:312").Select
    Selection.EntireRow.Hidden = False

' SortSummary Macro
'
    Range("B10:K106").Select
    Selection.Sort Key1:=Range("B11"), Order1:=xlAscending, Header:=xlGuess,
_
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("B113:K209").Select
    Selection.Sort Key1:=Range("B114"), Order1:=xlAscending,
Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("B216:K312").Select
    Selection.Sort Key1:=Range("B217"), Order1:=xlAscending,
Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' HideBlankRows Macro
'
'
Const MYCOL As String = "B"
Const STARTROW As Long = 11
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
    .Rows(STARTROW).Insert
    .UsedRange
With Range(.Cells(STARTROW, MYCOL), .Cells(Rows.Count, _
      MYCOL).End(xlUp).Offset(-1, 0))
        .AutoFilter Field:=1, Criteria1:="=", _
                Operator:=xlOr, Criteria2:="=0"
        On Error Resume Next
        Set rng = .SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        .AutoFilter Field:=1
    End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
    Range("A1").Select
ActiveSheet.Protect Password:="xxxx"
End Sub


"Charles Williams" <Charles@DecisionModels.com> wrote in message
news:Ogbj7QVGEHA.3880@TK2MSFTNGP10.phx.gbl...
> Hi Karl,
>
> Check that you have
>
> Application.calculation=xlManual
>
> ... your code
>
> Application.calculation=xlautomatic
>
>
> regards
> Charles
> ______________________
> Decision Models
> FastExcel Version 2 now available.
> www.DecisionModels.com/FxlV2WhatsNew.htm
>
> "Karl Burrows" <kfb1@spambellsouth.net> wrote in message
> news:SXrbc.17144$xW1.3997@bignews4.bellsouth.net...
> > Hi!
> >
> > I created several worksheets that contained huge lists, so I created a
> > dynamic named range.  Now that the name has been inserted in my array
> > formulas, my Macro runs very slowly.  The Macro unprotects one of my
> sheets,
> > sorts the data in a table, hides the blank rows and the protects the
sheet
> > again.  There are no related named range formulas in that worksheet, so
> I'm
> > not sure why it would be affected.  The Macro ran quickly when I had a
> > defined range of data.
> >
> > Would giving a named range like 'Date' and having text in my workbook
with
> > 'Date' create conflicts?  Could this affect my Macros?
> >
> > I'll be more than happy to send the formulas and Macro if you want to
look
> > at them further.
> >
> > Thanks!
> >
> >
>
>


0
kfb1 (138)
4/4/2004 5:03:48 PM
Reply:

Similar Artilces:

Macro Function
How do I create a new function in Excel? -- Tomas Hi Thomas, You can have a look at this link: http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html Regards, Bondi THANKS!!!! Very good tip -- Tomas "Bondi" skrev: > Hi Thomas, > > You can have a look at this link: > > http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html > > Regards, > Bondi > > ...

CRM 3.0 Install Problems
We're trying to upgrade a dev instance of CRM 1.2 to 3.0. It gets partway through the installation before it fails, giving this error message: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. Execution of a full-text operation failed. The parameter is incorrect. Looking at the log, it looks like this is where it fails: 15:00:38| Info| Disable full-text indexing 15:00:44| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Data.SqlClient.SqlException: Execution of a full-text operation failed....

Winmail.dat problem with SBS 2003..and yes i have read the other wiinmail posts
Ok, so I have read thru all the winmail.dat posts in this group and the outlook group, problem is most of them are geared towards ex 5.5. Well I have sbs 2003 and I have one user in my entire company that can not send a excell spreadsheet to one recp. without outlook 2003 i converting into a winmail.dat. I have tryed everyhting I can think of. I reinstalled outlook. I have sett all the options to send as plain text globally and for that contact that gets the winmail.dat files. I have been working on this for a while, but I do believe I checked the setting on exchange to allow the user to ch...

how do you hide a name from the globel address book?
in exchange 2K. Thanks ADUC Properties of the user Exch Advanced Check the Hide from Address Lists box On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" <Say-no-to-spam@hotmail.com> wrote: >in exchange 2K. > >Thanks > Thanks. Sorry I missed it. "Andy David - Exchange MVP" <adavid@pleasekeepinngcheesebucket.com> wrote in message news:aii4k014g54q5v7dic5a7r568lq31moanr@4ax.com... > ADUC > Properties of the user > Exch Advanced > Check the Hide from Address Lists box > > > On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" &...

Macro is not logging out of Great Plains
I created a macro to login, run checklinks and then log out. I set it up in the Task Scheduler on my system to run after hours, using a bat file. It worked for several months, and then in January it didn't finish the macro and log out. I would come in the morning, and I would still be logged in and the scheduled task was still running. I had to end task and delete my login in Great Plains. I have tried recreating the macro, I have tried running it on the server, and I am still having the same problem. In most cases, the report is generated - so I know the checklinks are finishing...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

Problems after kb890830 and kb978542 updates
After windows Vista installed kb890830 and kb978542 I noticed 4 problems so far. 1. MSN Service not available, error code: 800706b5. (This happened with previous updates and I fixed the issue by doing a system restore to a previous date.) 2.When I started internet explorer (ver 8.0.6001.18904) I got a message my default search engine was corrupted by another program. 3.I tried the usual system restore and found there were no restore points available 4. I had some unknown problem trying to log into this site. It kept giving a site not available message. I am running windows vis...

Cell Reference Problem with Network
Let's say I have a spreadsheet with two worksheets = SheetA and SheetB. SheetA might reference a cell in SheetB with a formula like =SheetB!A1 But when I move this to the network the reference changes to include the network drive and file name like: ='Z:\FOLDER\[FILE]SheetB!A1 the file may move from my laptop to the network several times and this becomes completely confusion as the reference looks, not within the same spreadsheet which is what I want it to to, but for another file out on the network. How do I explicitly reference a cell within a difference worksheet but alwa...

Macro
Is it possible to add a macro as a custom POS button? Thank you, Sylvain ...

Converting From ADO.Net XML to ADO problem
Using the method described in Microsofts Knowledge Base article http://support.microsoft.com/default.aspx?scid=kb;en-us;316337 to convert an XML file created by a DataSet into an XML file read by ADO. This seems pretty straight forward and the ADO recordset *opens* without error, however it's quite empty. Any attempt to use it (e.g. MoveFirst or RecordCount) causes an E_FAIL error. The translated XML file has lots of data when viewed with a browser. The code is below... DataSet ds = new DataSet(); try { SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM MyTable", SqlCo...

New entity
when i attach a contact to an account i have an option - to choose an account from the list, or to create a new one. but i dont have this option when i work with two custom entities (1 to many relationships). only to choose from existing when i want a assign a primary entity to a secondary one. is there a workarround? this way makes me press much more buttons in different forms. i have secondary objects already created due to my business proccess, so i need to go to the primary entity, create an object, then to go back, to open a secondary one and to attach the primary one to it. thanks ...

Problems with Crystal Reports
Hi everyone We are currently encountering a problem with Crystal Reports on MSCRM. Launching one takes about 10-15 seconds before data is displayed (even if only 1 record is retrieved). The standard Accounts reports are worse, we get a Timed Out error everytime. We are deploying MSCRM on 2 servers - CRM Server has dual procs and 1GB RAM. Any suggestions are very much appreciated. Thanks in advance. Regards. Hon Having Crystal reports in CRM 1.0 timeout is a known bug. (Happens after you try to retrieve more than about 10K records.) There is a private hotfix. It will also be fi...

Macro for checkbox (2) #2
Hi Still does not work new message is Sub or function not defined. I normaly just record macros so this is a bid more of a chalange Sub Macro1() ' ' If CheckBoxes(46).Value = True Then Range("O54").FormulaR1C1 = "50000" Range("T54").FormulaR1C1 = "4000" Range("Y54").FormulaR1C1 = "4000" Range("AD54").FormulaR1C1 = "4000" Range("AI54").FormulaR1C1 = "4000" Range("AN54").FormulaR1C1 = "4000" Range("AS54").FormulaR1C1 = "...

Problem with line Chart
I have the following data: Date Value 1/07 700 3/07 550 9/07 600 10/07 550 7/08 500 9/08 700 2/09 750 8/09 600 I would like to create a simple line chart connecting the Value points. Note that the points correspond to random dates (not evenly spaced). How can I create a line chart that has time on the x-axis, (say shown in months) with a line connecting the Value points, which are shown at various locations in time. Soun...

Urgent OWA 2K3 Setup Problem
I am fairly new to Exchange 2003 and IIS so this may be a silly question. I have a single 2003 Domain and Exchange organisation that contains 2 backend servers, one is for staff the other students. The default email policy is set for e.g user@emailaddress1 a second policy is set to user@emailaddress2 which is applied to all users on server2. I have configured OWA on Server1 which works fine but can't access any mailboxes on Server2. This is where I get a little confused! The settings on the default http virtual servers on both servers point to the default email policy but the mo...

OWA problem on front-end server
I'm currently running Exchange 2003 over Windows 2000 SP4 with a front-end server handling all the OWA and SSL. I've foung that any email ending with ..EML as opposed to .EML (note the extra dot) gets a 404 page not found. However if I log into OWA on a backend server the same email with the ..EML opens no problem. If anyone has some insight into this problem I'd be greatly appreciated. Take a look at: 320089 XCCC: The URLScan Utility Does Not Allow You to Open Messages in OWA http://support.microsoft.com/?id=320089 Thanks, Richard Roddy Microsoft Exchange Support This...

Clear Format problem
I am trying to write a macro to clear ALL format in a range of cell. However, I run into a problem. I am using the Selection.ClearFormats command and it works in most cases, but fails when multiple format are applied to the cell. For example if a cell only contains "Hello wonderful world" all in bold than ClearFormats will remove the bold formatting. But if the cell contains "Hello wonderful world" where only "wonderful" is in bold, than ClearFormats does not work and the word "wonderful" remains in bold. Does anyone know how to clea...

Why doesn't indirect work with a dynamic range created using offse
Hi Has anyone else experienced this issue or I am doing something wrong. I have a series of dynamic range names defined using offset. For example the name USCities is defined as =offset(AA1,0,0,Counta(AA:AA),1) and the name Europeancities is defined as =offset(AB1,0,0,counta(AB:AB),1) In column A, dataentry into the cells is limited to USCities or Europeancities. In column B, I want to limit data entry to the range name appearing in column A. For example if USCities is selected in A1, data entry in cell B1 is to be limited to those cells forming the USCities range name. I have tri...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

VBA Range Formula
Can anybody tell me how to get this to work? In other words, how do I reference my procedure variables in a cell formula? Thanks! Sub Marktest() Set b = Cells.Find("Total", , xlValues) Set c = Range("C5") Range(b.Offset(-1, 2), c).Formula = _ "=sumproduct(($B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*$C$1:$C$3)" End Sub I'd do something like: Option Explicit Sub Marktest2() Dim myRng As Range Dim b As Range Dim c As Range With ActiveSheet Set b = .Cells.Find("Total", , xlValues) Set...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...

Shared Calendar problems
We have created a secondary mailbox which includes a calendar for booking a conference room. If I want to book the room, I arrange a meeting with the room (using a modified calendar form) and a secretary accepts the meeting. It appears that something in my modified form is causing a mistake, as when the appointment is viewed (by anyone) the meeting time changes to the current time and if the person viewing it closes the appointment down they get the option to save the changes - thus updating the calendar and clearly causing a few nightmares with the booking! Any suggestions as to where my form...

Problems with Colour Printing
Hi there, I have a spreadsheet with multiple sheets containing colour formatting (including graphs) that is regularly printed out in colour and greyscale. The problem I have is that when I need to print out several sheets simultaneously I uncheck the "Greyscale" option under print options and have the print job come out in colour. To print multiple selected sheets in colour I need to change the print options for each individual sheet (about 10 in all) to colour (non- greyscale). One strange peculiarity of this is that some of the sheets will not need to be "Unchecked"...

Problem with "New Message to Contact"
I've created an Exchange public folder, which contains 50 contact folders. (This allows us to allow different regions access to just their customers.) I want to send a mail message to a group of customers. So I highlight all the contacts with e-mail addresses, go to the Actions menu and choose "New Message to Contact". I get the following error: "You do not have the permission to send the message on behalf of the specified user." I am the owner of the folder! This method works in a different Exchange public folder, just not in our customer folders. It does...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...