Posts by Mavyak

    I have the following function in a worksheet module:


    It takes that code 32 seconds to run. The rows delete immediately. The columns are what takes so long. There are 3481 rows in the spreadsheet. A vast majority of them have data validation. Here is the code that runs prior to this function:



    As you can see, I've tried three other commands (all commented out) before posting here but they didn't reduce the time it takes to complete the function above. What factors should I be looking at to identify why it is taking so long to delete seven columns and seven rows?

    Re: Microsoft Text Driver And Thousands Separator


    Any luck on this? If not, say so and we can probably work up some code that fixes the commas after the recordset has been copied into Excel. I'd also like to see the SQL and verify that a replace function won't work.


    Lemme know,


    Mav[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Looks like the function is not available using that driver (as I saw you also discovered on a VB.NET forum). Try replacing your replace function with this:


    SELECT IIF(InStr(<Current Field Name>, ',') > 0, Left(<Current Field Name>, InStr(<Current Field Name>, ',') - 1) & Right(<Current Field Name>, Len(<Current Field Name>) - InStr(<Current Field Name>, ',')), <Current Field Name>) As <Aliased Field Name>


    It's a very laborious work-around but I have verified it works on my machine.

    Re: Hide Macro List From User


    I wasn't aware of the Option Private statement. I would recommend doing a find/replace (Ctrl+H) to change all "Private Sub" strings to "Sub". That should leave you with public subs but a private module.

    Re: Formula Which Counts Items For Variable Number Of Attributes


    I don't know how to do it with a formula but it sounds like Autofilter would get you what you want and you wouldn't even need the second tab. Just throwing out a potential alternative solution.

    Re: Detect how excel was started


    Quoted From Help:

    Re: Finding First Empty Cell In A 4x4 Grid





    Edit: StephenR's is more efficient.
    Edit#2: But alas, Shg's pwnz.

    Re: Unique Values From Selected Range


    I've got the function linked to a button added to my right-click menu. I've been using it so long it's just faster for me to work by rote. At least it was faster for me until my code started halting.

    Re: Unique Values From Selected Range


    It's primarily a function I use when using Excel as a glorified clipboard between some proprietary software and MS Access. I've got another function that takes the unique list and copies it to my clipboard formatted as an "In" clause to paste as a where argument in MS Access.

    I use the following code to extract a unique list of values and paste the list to a specified range:



    The code gets interrupted at this line:

    Code
    1. If Not r Is Nothing Then


    with the following error: "Code execution has been interrupted."


    When I click debug, the line above is highlighted. If I click the sideways triangle to resume code execution it finishes normally. What can I do to stop my code from being interrupted?


    note: I created this code in Excel 2000 with Windows 2000 but am now running it in Excel 2003 on Windows XP.

    Re: User To Input In Textbox And Run Against Sql Server


    Quote

    Now I am getting a different situation in which I get an error code 3265 'item cannot be found in the collection corresponding to the requested name or ordinal'


    According to the SQL used to create the recordset:

    Code
    1. strSQL = "SELECT domain FROM Domain_List_TEST ORDER BY domain"
    2. rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText


    LAW_FIRMS and NickName were never selected. There is only one field in the recordset. That field is DOMAIN.


    Quote

    I tried doing a rst.recordcount and it gives me -1...what is that supposed to mean? I have 172 records in that table.


    I believe only Static and Dynamic recordsets accurately reflect a recordcount. ForwardOnly and Keyset do not. I'm not 100% sure on that though.

    Re: Running A Spreadsheet With 3 Calendars


    This still has me a bit confused. The only way for the code to reach the line where it is failing is if the recordset is in a state other than open? And if the recordset is open then a message box is displayed saying it is closed?