Excel Slow performance

[Svmaxcel]

I get customer survey data and collate it for atlesst a month.
There are around 300000 rows and columns are from A to AB.
I added some formula for emp look up and scores which increased 10 more columns.
I have made it a excel table so that formulas are added automatically when new data is added.
file size is around 52 Mb in binary format

Issues:
1: big file size
2: Super Slow Performance
3: Unable to filter data as when I click on filter arrows, it freezes for 3-4 min and sometimes display some filters or sometimes freezes completely.
4: in case I make any change in file like deleting a row, excel sticks on calculating and hangs up, after that closing excel doesn't work, I have to log off from system in order to close excel.(as its a corporate system, task manager is disabled)
5: opening file takes a long time.
6: sometimes I get error that excel cannot complete the task due to less resources available.
7: there are some other issues like the system freezes when excel calculates data and even the mouse sometimes lags.

What have I done for this.
Made a new file manually.
Copied all data as values, but did formulation for my extra columns.
All other files works fine.
System also works fine.
Same file freezes on other system also.
There are no array formulas, there are common formula of index match, date, week month
Reinstalled Ms office.
Tried same file on Ms office 2013.
I am using ms off 2007.
All add on disabled.
System ram is 8gb.
Enough space on HDD.
We have Intel i5 PRC with 2.4 GHz
Closing all opened programs and running file has the same issue.
Win 7 already reinstalled.
Tried pasting all data as Value(including formula), that shows improvement a little bit.

Need your help as to what can be the issue

[Jan Karel Pieterse]

IMO you should be looking into other means of enriching your data than by using formulas. One way is using MSAccess to hold both the survey data and the employee data so you can do a query which joins those two tables to get the related information. You can then get the data from that query into an Excel pivottable to do reporting which will perform well.

[BKuerzinger]

Svmaxcel,

I agree with Jan Karel Pieterse you should probably look outside plain Excel. That being said if it needs to be Excel maybe you could try approaching the problem with Microsoft's PowerPivot / PowerQuery Excel Addins, which are able to work with higher data loads but also come with a learning curve. I am not sure if the higher performance of these Addins will be enough, but might be worth a try if you don't want to go to MS Access / SQL directly.

Kind regards,
Bernd

[Svmaxcel]

Thanks for the response.
Excel has capacity of around 1000000 rows, so how come it freezes in just 2 to 300000 rows.

[Jan Karel Pieterse]

Well you do have to take into account that you also have a large number of columns. And having hundreds of thousands of vlookups is quite a challenge for Excel.

[Paul_Hossler]

Do you really need to have a lot of formulas?

If the data remains unchanged once it's been calculated, maybe storing just the result would eliminate the need to a lot of recalculating

For example, if you load a product number, and then use a VLookup() to get it's description you could replace the formula with the result

Use a Sub to populate static data with data instead of a formula

[Svmaxcel]

Thanks for the suggestion, can you please help me with iferror index match formula in a loop, so that once data is detected in Column A, Formula will automatically run and store the values in stead of formula
For example, I have a sheet with emp names and I'd in sheet names Mapping.
In Data sheet I have emp I'd only and want to look up emp names
The emp I'd are in column A and I want there names in column b,

[Svmaxcel]

Just to test speed of vba I used do until loop with if formula and was surprised to see it taking a very very long time compared to Excel in build if formula.
My test column had only 6000 rows and VBA took around 3 min to complete.
On the other hand excel if was completed within a blink of an eye.

I would certainly use values for fields which are already calculated.
Please help me with VBA to copy paste text as values only.

[mdmackillop]

Please help me with VBA to copy paste text as values only.

With ActiveSheet
.Columns(6).Value = .Columns(6).Value
End With

[offthelip]

Just to test speed of vba I used do until loop with if formula and was surprised to see it taking a very very long time compared to Excel in build if formula.
My test column had only 6000 rows and VBA took around 3 min to complete.

This sounds like you are not using VBA in the fastest way possible. If you are accessing cells 6000 times then it will take a long time, the way to get VBA to work rapidly is to load the range into a variant array process all the data in VBA and write it back, 6000 line should only take milliseconds if done correctly.
A little while ago I started this thread which compared times for doing calculations on a worksheet, different ways of doing the same thing made differences between 0.34 secs and 18.5 secs
Have alook to see how to speed up yor code.

http://www.vbaexpress.com/forum/show...ray&highlight=

[Svmaxcel]

Dim RNG as range
Rng = sheets1.range("B2")
Rng.select
Do until RNG =""
Rng =Activecell.offset(1,0)
Worksheet function.vlookup(active cell.offset(0,-3), treple, 3,0)
Loop
End sub

This is an example of what I am doing but super slow speed.
I checked the link, but actually didn't understand well the difference in slow and fast speed.
I would appreciate if you can help me with the code

[mdmackillop]

I don't follow your last code, Can you post a small sample workbook to demonstrate.

[offthelip]

Your code doesn't compile and I can't see that it actually does anything. However as I expected it is very very slow code. Remember that every access between VBA and the Excel worksheet will take quite a long time, virtually every line of your code accesses the worksheet. Most important you are accessing the worksheet in a loop. This means there will thousands of access to the worksheet.
as a demonstration I have written this code which searches through all the data in column B of sheet2 to find a match for it in column B of sheet1. and then copies columns A to C of sheet 1 to D to F of sheet 2

with 4000 lines on sheet 1 and 1400 on sheet 2 it took 4.5 seconds on my machine,

Sub speedy()
Dim StartTime As Double
StartTime = Timer
With Worksheets("sheet1") ' this access the worksheet
 lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this access the worksheet
 inarr = Range(.Cells(1, 1), .Cells(lastrow, 3)) ' this access the worksheet
End With
With Worksheets("sheet2") ' this access the worksheet
 lastrow2 = Cells(Rows.Count, "A").End(xlUp).Row ' this access the worksheet
 matcharr = Range(.Cells(1, 1), .Cells(lastrow2, 3)) ' this access the worksheet
 outarr = Range(.Cells(1, 4), .Cells(lastrow2, 6)) ' this access the worksheet
For i = 2 To lastrow2  ' the loop starts here and nothing in the loop access the worksheet
  For j = 2 To lastrow
   If matcharr(i, 2) = inarr(j, 2) Then
     For k = 1 To 3
      outarr(i, k) = inarr(j, k)
     Next k
     Exit For
   End If
  Next j
Next i  ' loop ends here


Range(.Cells(1, 4), .Cells(lastrow2, 6)) = outarr ' this access the worksheet.


End With


SecondsElapsed = Round(Timer - StartTime, 2)


MsgBox SecondsElapsed
 
End Sub

[SamT]

I can't figure out what that does... Column B does not have a column 3 columns to its left, active cell.offset(0,-3)

A little out of my league, And it's not tested, so you may have to tweak it. Actually, since I am guessing a lot about your Project, you will definitely have to tweak it,

Option Explicit

Sub SamT()
Dim rngSheet1_B
Dim rngSheet1_E 'Just guessing as to the column you want the results in
Dim rngTreple_Col1 'Just guessing that "Treple" is in fact a Named Range
Dim rngTreple_Col3

Dim i As Long
Dim x As Long


With Sheets("Sheet1")
rngSheet1_B = Range(.Range("B2"), .Range("B2").End(xlDown)).Value
End With
ReDim rngSheet1_E(UBound(rngSheet1_B))

rngTreple_Col1 = Range("Treple").Columns(1).Value
rngTreple_Col3 = Range("Treple").Columns(3).Value

For i = 1 To UBound(rngSheet1_B)
  x = WorksheetFunction.Match(rngSheet1_B(i), rngTreple_Col1, 0)
  rngSheet1_E(i) = rngTreple_Col3(x)
Next i

Sheets("Sheet1").Range("E").Resize(UBound(rngSheet1_E), 1) = rngSheet1_E

End Sub

[Svmaxcel]

indexmatch.xlsx
File attached
There are only few entries I made for example
These goes as large as 76000 rows
I would like to know how can I use VBA to do the formulation

[Paul_Hossler]

I'd do it each day, or at least in batchs


In the attachment, for the rows that are in the Selection, L - AB will be computed when you run the macro after you put in values for A - K


Example --

Select A8 - A11 and run the macro

This will re-compute any previous row, so unless the data's changed, it's faster to just select rows with new data entered

Option Explicit


Sub AddData()
    Dim rData As Range, rRow As Range, rDates As Range, rNames As Range, rSkills As Range
    Dim i As Long
    If Not TypeOf Selection Is Range Then Exit Sub
        
    Set rData = Worksheets("Data").Range("A1").CurrentRegion
    Set rDates = Worksheets("Mapping").Range("I1").CurrentRegion
    Set rNames = Worksheets("Mapping").Range("A1").CurrentRegion
    Set rNames = rNames.Cells(1, 2).Resize(rNames.Rows.Count, rNames.Columns.Count - 1) ' emp number is in A
    Set rSkills = Worksheets("Mapping").Range("P1").CurrentRegion
    
    Application.ScreenUpdating = False
    
    For Each rRow In Intersect(Selection.EntireRow, rData).Rows
        With rRow
            If .Row = 1 Then GoTo GetNext
            If Len(.Cells(1).Value) = 0 Then GoTo GetNext
            
            For i = 12 To 21
                .Cells(i).Value = "-"
            Next I
              
            On Error Resume Next
            .Cells(12).Value = Format(.Cells(2).Value, "DDDD")
            .Cells(13).Value = Format(.Cells(2).Value, "MMM-YY")
            .Cells(14).Value = "WK" & Application.WorksheetFunction.WeekNum(.Cells(2).Value)
            .Cells(15).Value = Application.WorksheetFunction.VLookup(CLng(.Cells(2).Value), rDates, 5, False)
            .Cells(16).Value = Application.WorksheetFunction.VLookup(CLng(.Cells(2).Value), rDates, 6, False)
        
            .Cells(17).Value = Application.WorksheetFunction.VLookup(.Cells(3).Value, rNames, 2, False)
            .Cells(18).Value = Application.WorksheetFunction.VLookup(.Cells(3).Value, rNames, 4, False)
            .Cells(19).Value = Application.WorksheetFunction.VLookup(.Cells(3).Value, rNames, 5, False)
        
            .Cells(20).Value = Application.WorksheetFunction.VLookup(.Cells(1).Value, rSkills, 4, False)
            .Cells(21).Value = Application.WorksheetFunction.VLookup(.Cells(1).Value, rSkills, 5, False)
        
            .Cells(22).Value = .Cells(4).Value * .Cells(5).Value
            .Cells(23).Value = .Cells(4).Value * .Cells(6).Value
            .Cells(24).Value = .Cells(4).Value * .Cells(7).Value
            .Cells(25).Value = .Cells(4).Value * .Cells(8).Value
            .Cells(26).Value = .Cells(9).Value / 600
            .Cells(27).Value = .Cells(10).Value / 60
            .Cells(28).Value = .Cells(11).Value / 60
            On Error GoTo 0
        
        End With
GetNext:
    Next
    Application.ScreenUpdating = True

End Sub

[Svmaxcel]

Fantastic work!!!!!
Had some questions here.
1) should we use Vlookup or Index/Match, people usually say that Index/Match is much more faster.
2) Iferror is not used in any formula, in case there Is an error(values not found), what will happen.
3) Can we create a new Sheet after the execution is done with the format attached.newdata.xlsx

[offthelip]

Paul

Most important you are accessing the worksheet in a loop.

As I said earlier the way to really speed up the code is to avoid accessing the worksheet in a loop. You are still doing multiple accesses to the worksheet in a loop, all you have really done is reduce the loop to what has changed by selecting the rows that have changed. Unfortuantely I don't have time to rework this using variant arrays at the moment, but I am sure it would be much faster.

[snb]

100% Arrays:

Sub M_snb()
   sn = Sheets("Data").Cells(1).CurrentRegion
   sp = Sheets("Mapping").UsedRange
   
   For j = 2 To UBound(sn)
       For jj = 2 To UBound(sp)
          If sn(j, 3) = sp(jj, 1) Then
             sn(j, 17) = sp(jj, 2)
             sn(j, 18) = sp(jj, 4)
             sn(j, 19) = sp(jj, 5)
             Exit For
          End If
      Next
   
       For jj = 2 To UBound(sp)
          If sn(j, 1) = sp(jj, 16) Then
             sn(j, 20) = sp(jj, 20)
             sn(j, 21) = sp(jj, 21)
             Exit For
          End If
      Next
      
      w_00 = sn(j, 2) - Weekday(sn(j, 2))
      For jj = 22 To 28
        If jj < 27 Then sn(j, jj - 10) = Format(Choose(jj - 21, sn(j, 2), sn(j, 2), sn(j, 2), w_00 + 1, w_00 + 7), Choose(jj - 21, "dddd", "'mmm-yy", "\Wk ww", "\WB dd-mm-yyyy", "\WE dd-mm-yyyy"))
        sn(j, jj) = Choose(jj - 21, sn(j, 4) * sn(j, 5), sn(j, 4) * sn(j, 6), sn(j, 4) * sn(j, 7), sn(j, 4) * sn(j, 8), sn(j, 9) / 600, sn(j, 10) / 60, sn(j, 11) / 60)
      Next
   Next
   
   Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn
End Sub

Sheet "mapping" columns I:N are redundant.
You don't even need screenupdating =false.

[offthelip]

Snb: Beautifully done, I couldn't have put it better myself.