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

[SamT]

Paul, I am a lazy typist.

 Dim WsF as Object
Set WsF = Applcation.WorksheetFunction
'
'
.Cells(14).Value = "WK" & Wsf.WeekNum(.Cells(2).Value)

For daily use

Sub AddData(Optional NewData As Range)
'
'
If Not NewData is Nothing Then NewData.Select
    If Not TypeOf Selection Is Range Then Exit Sub 
'
'

Public Sub Run_AddData_On_NewData()
Dim NewData As Range
Set NewData = AppropriateColumn.Find(First Formula) 'Edit to Code
Set NewData = Range(NewData, NewData.End.(xlDown))

AddData NewData

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

[Paul_Hossler]

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.

1 - People say that. VLookup is simpler

2 - This fills in a "-" just in case, and the On Error Resume Next says to ignore any error, which leaves the "-"

            For i = 12 To 28
                .Cells(i).Value = "-"
            Next I
              
            On Error Resume Next

3 - Yes. See 'Roster' in the attachment


I don't like having to VLookup() the WB and WE values since they can be computed from the Date -- what are the rules to determine WB and WE?

Then you wouldn't need Mapping I:N at all

Option Explicit
'     1       2      3    4            5                  6                   7                   8                      9                           10                             11
'Skill Group Date    ID  Calls   Avg Handle Time In  Avg Talk Time In    Avg Hold Time In    Avg Wrap Time In    Not Ready Time (Per Agent)  Logged On Time (Per Agent)  Available Time (Per Agent)
' 12  13      14         15             16           17          18            19           20          21            22                  23             24               25              26                  27                      28
'Day Month   Week    Week Beginning  Week Ending Agent Name  Team Manager    Location    SkillSet    Language    Total Handle Time   Total Talk Time Total Hold Time Total Wrap Time Not Ready Time(min) Logged On Time(min) Available Time(min)

Sub AddData()
    Dim rData As Range, rRow As Range, rDates As Range, rNames As Range, rSkills As Range, rRoster As Range, rRoster1 As Range
    Dim i As Long
    Dim oWSF As WorksheetFunction
    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
    Set oWSF = Application.WorksheetFunction
    
    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 28
                .Cells(i).Value = "-"
            Next i
              
            On Error Resume Next
            '  1      2    3      4           5               6
            'Date    Day Month   Week    Week Beginning  Week Ending
            .Cells(12).Value = Format(.Cells(2).Value, "DDDD")
            .Cells(13).Value = Format(.Cells(2).Value, "MMM-YY")
            .Cells(14).Value = "WK" & oWSF.WeekNum(.Cells(2).Value)
            .Cells(15).Value = oWSF.VLookup(CLng(.Cells(2).Value), rDates, 5, False)
            .Cells(16).Value = oWSF.VLookup(CLng(.Cells(2).Value), rDates, 6, False)
        
            '         1   2          3          4          5            6
            'EMP ID  ID  Name    Designation Supervisor  Location    Team Name
            .Cells(17).Value = oWSF.VLookup(.Cells(3).Value, rNames, 2, False)
            .Cells(18).Value = oWSF.VLookup(.Cells(3).Value, rNames, 4, False)
            .Cells(19).Value = oWSF.VLookup(.Cells(3).Value, rNames, 5, False)
        
            '    1            2         3        4             5
            'Skill_Name  Department  Product Skill_LOB   Skill_Language
            .Cells(20).Value = oWSF.VLookup(.Cells(1).Value, rSkills, 4, False)
            .Cells(21).Value = oWSF.VLookup(.Cells(1).Value, rSkills, 5, False)
        
            If .Cells(5).Value <> 0 Then .Cells(22).Value = .Cells(4).Value * .Cells(5).Value
            If .Cells(6).Value <> 0 Then .Cells(23).Value = .Cells(4).Value * .Cells(6).Value
            If .Cells(7).Value <> 0 Then .Cells(24).Value = .Cells(4).Value * .Cells(7).Value
            If .Cells(8).Value <> 0 Then .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
    'make roster worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Roster").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add.Name = "Roster"
    With rData
        .Columns(2).Copy Worksheets("Roster").Range("A1")
        .Columns(3).Copy Worksheets("Roster").Range("B1")
        .Columns(17).Copy Worksheets("Roster").Range("C1")
        .Columns(18).Copy Worksheets("Roster").Range("D1")
    End With
    
    Set rRoster = Worksheets("Roster").Range("A1").CurrentRegion
    With rRoster
        .EntireColumn.AutoFit
        Set rRoster1 = .Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count)
    End With
    
    With Worksheets("Roster").Sort
        .SortFields.Clear
        .SortFields.Add Key:=rRoster1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=rRoster1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rRoster
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

[Svmaxcel]

Thanks buddy.
You are write Vlookup is Simpler, but does it actually slows down things when compared to Index Match, if Vlookup is better option I don't mind using it.

There are no specific rules in WB and WB,
WB dates starts From Sunday
WE dates starts from Saturday

Attaching file from reference