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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
574
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Did you try...

Application.EnableEvents = False
Application.ScreenUpdating = False

at the end swith them back to true.

Application.EnableEvents = True
Application.ScreenUpdating = True
 

cmhoz

Active Member
Joined
Aug 20, 2006
Messages
268
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

Legend
Joined
May 15, 2003
Messages
29,536
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...)
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
574
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,840
Messages
5,489,191
Members
407,675
Latest member
meaghutter

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top