Does anyone know of a shorter, faster way to do this...
Results 1 to 6 of 6

Thread: Does anyone know of a shorter, faster way to do this...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Does anyone know of a shorter, faster way to do this...

    When a button on a userform is clicked, this code finds all rows satisifed by the startdate d1, and the endate, d2 values anywhere in a large spreadsheet from A1:G777, copies the values to a REPORT sheet and generates a form displaying the selected records. It's neat, BUT this assigning of variables for each row cell value selected makes the operation VERY slow - so slow that you can actually see the action taking place as the code selects and copies the records.

    Is there a way to do this which would be much faster and with less coding ?

    Here's the code:
    Code:
    Private Sub Runrep_Click()
     Application.EnableEvents = False
     Dim a As Date, w, wb, wz As Worksheet
     Dim r As Range, d1, d2 As Date
     Dim Lr, lRow As Long
     d1 = ALLSPENDING.TextBox1.Value
     d2 = ALLSPENDING.TextBox2.Value
    Set wz = Worksheets("REGISTER")
    wz.Activate
    With wz
     wz.Range("C2:C2").Select
     Do
     a = ActiveCell.Value
      ActiveCell.Offset(1, 0).Select
       If a > d2 Then Exit Do
       a = ActiveCell.Value
      If a >= d1 And a <= d2 Then
              Set w = Worksheets("REPORT")
                    lRow = w.Cells(Rows.Count, 1) _
                        .End(xlUp).Offset(1, 0).Row
                            With w
                                Dim d, c, da, ad, vo, vp, dp, dep, bb, rb
                                d = ActiveCell.Offset(0, -2).Value
                                c = ActiveCell.Offset(0, -1).Value
                                da = ActiveCell.Offset(0, 0).Value
                                ad = ActiveCell.Offset(0, 1).Value
                                vo = ActiveCell.Offset(0, 2).Value
                                vp = ActiveCell.Offset(0, 3).Value
                                dp = ActiveCell.Offset(0, 4).Value
                                dep = ActiveCell.Offset(0, 5).Value
                                bb = ActiveCell.Offset(0, 6).Value
                                rb = ActiveCell.Offset(0, 7).Value
                                    .Cells(lRow, 1).Value = d
                                    .Cells(lRow, 2).Value = c
                                    .Cells(lRow, 3).Value = da
                                    .Cells(lRow, 4).Value = ad
                                    .Cells(lRow, 5).Value = vo
                                    .Cells(lRow, 6).Value = vp
                                    .Cells(lRow, 7).Value = dp
                                    .Cells(lRow, 8).Value = dep
                                    .Cells(lRow, 9).Value = bb
                                    .Cells(lRow, 10).Value = rb
                                    ALLSPEND.TextBox1.Value = d1 'ALLOTHERSPENDING.TextBox1.Value
                                    ALLSPEND.TextBox2.Value = d2 'ALLOTHERSPENDING.TextBox2.Value
                            End With
       Else
       End If
     Loop Until a > d2
    End With
    Application.EnableEvents = True
    ALLSPEND.Show
    End Sub
    Thanks for anybody who help make this easier and most of all faster with a minimal of coding!

    CR

  2. #2
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Does anyone know of a shorter, faster way to do this...

    Did you try...

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    at the end swith them back to true.

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Jeff

  3. #3
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Does anyone know of a shorter, faster way to do this...

    Can you use filters instead?

    Turn on the 'auto filter' and use the dates entered by your user to filter the data & the copy the visible cells and paste into your other sheet...

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Does anyone know of a shorter, faster way to do this...

    A big problem is selecting/activating so much, neither of which are generally necessary.

    You might also want to look at your variable declaration:

    Code:
     Dim a As Date, w, wb, wz As Worksheet
     Dim r As Range, d1, d2 As Date
     Dim Lr, lRow As Long
    As you have it w, wb, d1 & Lr are variants. It's also better, but not necessary to keep like declarations together.

    HTH,

    (Sorry for not contributing more at the moment...)

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Does anyone know of a shorter, faster way to do this...

    Jeff - amazing! Two lines of code and it becomes instantaneous. Very fast now. Perfect solution. Thanks a million for helping with this.

    CR
    Kingwood, TX

  6. #6
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Does anyone know of a shorter, faster way to do this...

    cmhoz,

    Maybe something like this is what you are seeking...

    http://www.mrexcel.com/forum/showthread.php?t=363258
    Jeff

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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