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

[chazrab]

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:

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

 

[jeffreybrown]

Did you try...

Application.EnableEvents = False
Application.ScreenUpdating = False

at the end swith them back to true.

Application.EnableEvents = True
Application.ScreenUpdating = True

 

[cmhoz]

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...

 

[Smitty]

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

You might also want to look at your variable declaration:

 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...)

 

[chazrab]

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

 

[jeffreybrown]

cmhoz,

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

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