+ Reply to Thread
Results 1 to 3 of 3

Amending code to add array

Hybrid View

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    12

    Amending code to add array

    Hello everyone, I have been using the below code successfully on a handful of worksheets. However, when I use this code on a number of worksheets, it takes a very very long time to complete. I was wondering if instead of utilizing the If .Name <> code, I could insert an array of worksheets that I would like this code to run on. Is that possible? I am looking to minimize the run time of the macro, and I believe using an array may help that. If anyone could help that would be great! Any other recommendations are welcome as well!!

    Sub Detaildelete()
     Dim LR As Long, I As Long, ws As Worksheet
     For Each ws In ThisWorkbook.Worksheets
        With ws
            If .Name <> "Details" And .Name <> "Summary" Then
               LR = .Range("A" & .Rows.Count).End(xlUp).Row
                For I = LR To 16 Step -1
                    If Trim(.Range("E" & I)) <> Trim(.Range("B8")) Or Trim(.Range("G" & I)) <> Trim(.Range("B9")) Or Trim(.Range("K" & I)) _
                       <> Trim(.Range("B10")) Or Trim(.Range("M" & I)) <> Trim(.Range("B11")) Then
                            .Rows(I).Delete
                    End If
                Next I
            End If
        End With
      Next ws
      MsgBox "Complete"
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Amending code to add array

    Changing to an array won't change the no of sheets the code runs on so won't change the running time.

    What could speed things up is filtering the data to get the rows you want to delete.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,234

    Re: Amending code to add array

    Hi dms,

    Here is a great link to help

    http://www.cpearson.com/excel/optimize.htm

    I'd try to ScreenUpdating = False first to see if that helps much.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help with amending code
    By ea223 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2013, 01:10 AM
  2. Help Amending a bit of VBA code
    By cldcp00 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2013, 04:48 PM
  3. Amending code
    By khalid79m in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2007, 03:23 PM
  4. [SOLVED] Help amending code
    By Steve in forum Excel General
    Replies: 4
    Last Post: 08-17-2005, 11:06 AM
  5. [SOLVED] Help with Amending this Code Please
    By paul_black27@hotmail.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1