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/showthread.php?60306-loading-a-variant-array&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]

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

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

[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

[Paul_Hossler]

Paul

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.

Yes, but as I said doing it in a daily batch would not take a lot of wall clock time

The first run of 100K+ rows might take some time of course, but I opted for the simplest, most easily understood and most easily maintained approach for the OP to self-maintain so I made it 'Formula-like'

I deliberately avoided a complicated, sophisticated VBA array approach since (while some members here could maintain it) I wanted the OP to have something simple that worked, even if not the highest performance

Personal Opinion: 99.9999% of the time, I see no value / need to have just a few lines of complicated, obscure, hard to read / maintain VBA.

[SamT]

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, 3) = 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.

Does this do the same thing

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)
If jj > 21 And jj < 29 Then GoTo jj2228
Exit For
ElseIf sn(j, 3) = sp(jj, 16) Then
sn(j, 20) = sp(jj, 20)
sn(j, 21) = sp(jj, 21)
If jj > 21 And jj < 29 Then GoTo jj2228
Exit For
End If

jj2228:
If jj > 21 And jj < 29 Then
w_00 = sn(j, 2) - Weekday(sn(j, 2))

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"))
End If

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)
End If
Next
Next

Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn
End Sub

[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

[snb]

@Sam T

No.

the first 2 loops filter in array sp (the counter jj represents the rows in array sp)

the last loop is independent of array sp. The counter jj represents the 'column' in array sn.

[Svmaxcel]

No words to describe your hard and superb work.
You guys are God of Excel and VBA

[snb]

Sub M_snb()
sn = Sheets("Data").Cells(1).CurrentRegion
sp = Sheets("Mapping").UsedRange
sf= split("dddd_'mmm-yy_\Wk ww_\WB dd-mm-yyyy_\WE dd-mm-yyyy","_")

For j = 2 To UBound(sn)
For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit for
next
sn(j, 17) = sp(jj, 2)
sn(j, 18) = sp(jj, 4)
sn(j, 19) = sp(jj, 5)

For jj = 2 To UBound(sp)
If sn(j, 1) = sp(jj, 16) Then exit for
next
sn(j, 20) = sp(jj, 20)
sn(j, 21) = sp(jj, 21)

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), sf(jj - 21))
sn(j, jj) = Choose(jj - 21, sn(j, 5) * sn(j, 4), sn(j, 6) * sn(j, 4), sn(j, 7) * sn(j, 4), sn(j, 8) * sn(j, 4), sn(j, 9) / 600, sn(j, 10) / 60, sn(j, 11) / 60)
Next
Next

Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn
End Sub

[offthelip]

Paul;

I deliberately avoided a complicated, sophisticated VBA array approach since (while some members here could maintain it) I wanted the OP to have something simple that worked, even if not the highest performance

Personal Opinion: 99.9999% of the time, I see no value / need to have just a few lines of complicated, obscure, hard to read / maintain VBA.

I agree with you totally except in this case where the title of the thread is "Excel Slow performance" in which case I believe the OP has expressed interest in knowing what is the fast way of doing things.

[snb]

When you apply F8 and the local window, I think the array approach is much easier to follow than the Excelformulae-in-VBA approach.
Maybe it takes some time the first time, but when grasping the code sets in, the learning process is much faster ( own experience).

[Paul_Hossler]

Paul;


I agree with you totally except in this case where the title of the thread is "Excel Slow performance" in which case I believe the OP has expressed interest in knowing what is the fast way of doing things.


I agree with you agreeing with me, and if each day or every time 300K+ lines might have to be processes, I'd re-think.

Since IN THIS CASE it appears that a relatively small amount data was appended incrementally daily or several times a day, IMVVVHO a simple, user-maintainable approach seemed best

It's a tradeoff

[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

[Paul_Hossler]

When you apply F8 and the local window, I think the array approach is much easier to follow than the Excel formulae-in-VBA approach.
Maybe it takes some time the first time, but when grasping the code sets in, the learning process is much faster (own experience).

For a very experienced coder (such as yourself) who is familiar with your coding style and arrays processing, and who codes for a living (or hobby??) I'd probably agree

However, for us regular people who might not look at the macro for a year (and only when there's a problem, or something had to be added) , I believe that it's better to err on the side of wordy-ness and simplicity as much as possible, even if performance takes a hit.

To mitigate the performance impact, I made the macro 'smart' (OK, only my opinion) enough to just use the selected 1,000 rows, instead of recalculating the other 299,000 rows

Running the non-array macro on the 1000 rows just added, would most likely have an imperceptible delay


Just my $0.02

[Paul_Hossler]

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

Well, I don't know if VLookup is slower than Match/Index, but I added the VBA equivalent (using arrays for the Mapping tables) A collection or dictionary seems like over-kill

I compute the WB/WE dates -- note that you have different formats for some

If you still need more performance, then I think you should go with one of the totally array approaches that others have suggested




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, rNames As Range, rSkills As Range, rRoster As Range, rRoster1 As Range
Dim i As Long
Dim oWSF As WorksheetFunction
Dim vIDs As Variant, vNames As Variant
Dim vSkill As Variant, vSkills As Variant

If Not TypeOf Selection Is Range Then Exit Sub

Set oWSF = Application.WorksheetFunction

Set rData = Worksheets("Data").Range("A1").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
vIDs = oWSF.Transpose(rNames.Columns(1).Value)
vNames = rNames.Value

Set rSkills = Worksheets("Mapping").Range("I1").CurrentRegion
vSkill = oWSF.Transpose(rSkills.Columns(1).Value)
vSkills = rSkills.Value
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 = "WB " & Format(.Cells(2).Value - oWSF.Weekday(.Cells(2).Value) + 1, "dd-mmm")
.Cells(16).Value = "WE " & Format(.Cells(2).Value - oWSF.Weekday(.Cells(2).Value) + 7, "dd-mmm")

' 1 2 3 4 5 6
'EMP ID ID Name Designation Supervisor Location Team Name
i = oWSF.Match(.Cells(3).Value, vIDs, 0)
.Cells(17).Value = vNames(i, 2)
.Cells(18).Value = vNames(i, 4)
.Cells(19).Value = vNames(i, 5)

' 1 2 3 4 5
'Skill_Name Department Product Skill_LOB Skill_Language
i = oWSF.Match(.Cells(1).Value, vSkill, 0)
.Cells(20).Value = vSkills(i, 4)
.Cells(21).Value = vSkills(i, 5)

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

[SamT]

@ sb,


Oh, I see. THanks for checking me.

[snb]

Using 2 two-dimensional arrays and 1 1-dimensional array I can't call 'complicated'.
Nor maintaining 22 lines of code of which most of the lines have the pattern x=y.
Even after 7 seven years.

[SamT]

@ Paul

However, for us regular people who might not look at the macro for a year (and only when there's a problem, or something had to be added) , I believe that it's better to err on the side of wordy-ness and simplicity as much as possible,
Just rename his variables to meaningful names and it is much easier to read and parse.


arrData = Sheets("Data").Cells(1).CurrentRegion
arrMapping = Sheets("Mapping").UsedRange
arrFormats= split("dddd_'mmm-yy_\Wk ww_\WB dd-mm-yyyy_\WE dd-mm-yyyy","_")

For DataRow = 2 To UBound(arrData)
For MappingRow = 2 To UBound(arrMapping)
If arrData(DataRow, 3) = arrMapping(MappingRow, 1) Then Exit For
Next
arrData(DataRow, 17) = arrMapping(MappingRow, 2)
arrData(DataRow, 18) = arrMapping(MappingRow, 4)
arrData(DataRow, 19) = arrMapping(MappingRow, 5)

For MappingRow = 2 To UBound(arrMapping)
If arrData(DataRow, 1) = arrMapping(MappingRow, 16) Then exit For
Next
arrData(DataRow, 20) = arrMapping(MappingRow, 20)
arrData(DataRow, 21) = arrMapping(MappingRow, 21)

A_Day = arrData(DataRow, 2) - Weekday(arrData(DataRow, 2))
For MappingRow = 22 To 28 'Possibly, no, probably, misnamed
If MappingRow < 27 Then arrData(DataRow, MappingRow - 10) = Format(Choose(MappingRow - 21, arrData(DataRow, 2), arrData(DataRow, 2), arrData(DataRow, 2), A_Day + 1, A_Day + 7), arrFormats(MappingRow - 21))
arrData(DataRow, MappingRow) = Choose(MappingRow - 21, arrData(DataRow, 5) * arrData(DataRow, 4), arrData(DataRow, 6) * arrData(DataRow, 4), arrData(DataRow, 7) * arrData(DataRow, 4), arrData(DataRow, 8) * arrData(DataRow, 4), arrData(DataRow, 9) / 600, arrData(DataRow, 10) / 60, arrData(DataRow, 11) / 60)
Next
Next

Sheets("Data").Cells(1).CurrentRegion.Offset(19) = arrData



If you want to go farther, you can take most of the Magic Numbers and replace them with Constants/Enums named after the respective Column heads

[offthelip]

When you apply F8 and the local window, I think the array approach is much easier to follow than the Excelformulae-in-VBA approach.
Maybe it takes some time the first time, but when grasping the code sets in, the learning process is much faster ( own experience).

I think snb has a very good point, when you look at the code it doesn't look any more complicated. If one has any experience using other programming languages then using arrays looks perfectly normal, it is the EXCEL bit, of ranges and cells that looks different.
Also because using arrays is so much faster than using ranges, I think it is worth learning how to do it. This is partly because doing things differently only when you need extra speed is not a very efficient way of working, especially when you only discover you need the extra speed having programmed it the slow way. Thus requiring reprogramming.
I always use variant arrays whenever I can. I never know when I might reuse a bit of code which was orignally coded as a once through routine but now becomes a multi loop routine.
Note; I do accept that my opinion is totally biased since I have been using computers for 49 years, in more languages than I could possibly count.

[Paul_Hossler]

Note; I do accept that my opinion is totally biased since I have been using computers for 49 years, in more languages than I could possibly count.

@offthelip --

And my opinion isn't????

I mended punch paper tapes with scotch tape to feed them into the reader

Then they came out with the new fangled 'key-to-disc' technology that eliminated having to punch cards and worry about dropping the box in the hallway



@SamT --

po-ta-to, po-tah-o

Yes, you and snb are perfectly correct -- you and snb and I could figure it out. Maybe the OP could also

If I were doing it for myself, I would have done it differently: Consts or eNums, an array (possibly) for the row, intelligent determination of what new data requires filling in, etc.

The examples and suggestions all seem to recompute data that has be computed.

My approach allows the selection of data to be computed. Yes, I'm sure that logic could be added to build the data array with only data that needs to be computed and logic to build the requested Roster sheet


Bottom Line:

I agree that we disagree

[snb]

We can't disagree on:

using 'select' slows down the code
using 'activate' slows down the code
worksheet interaction slows down the code

In the perspective of 'speed' (see the thread title) these ar the first 3 most influential recommendations: avoid 'select', avoid 'activate', avoid worksheet/document interaction.

After that improvements can be obtained by restructuring data, reducing loops,etc. but these are minor compared to the first 3 recommendations.

If you add the perspective 'TS knowledge' you introduce a perspective that is highly based on assumptions.
I can't tell by alias the competence level ot the TS.
From the start I therefore assume 'complete competence'.
That's why I give an answer without taking the 'TS competence' into account.
Only after the reaction of the TS on the provided suggestion I can make some assessment of his/her competence level.
If I get the impression the TS is intrigued by the suggestion and is willing to learn more I provide more explanation.

I also keep in mind that our suggestions are not only meant to serve the TS but also the 'thousands' of visitors that are looking for 'excellent examples' of VBA solutions in the decades to come.
The competence level of these visitors I am not aware of; so I prefer to provide them a suggestion that meets VBA criteria first.

BTW. rereading my code I still found 1 error (since it is so simple to read it is very simple to debug):


Sub M_snb()
sn = Sheets("Data").Cells(1).CurrentRegion
sp = Sheets("Mapping").UsedRange
sf= split("dddd_'mmm-yy_\Wk ww_\WB dd-mm-yyyy_\WE dd-mm-yyyy","_")

For j = 2 To UBound(sn)
For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next
sn(j, 17) = sp(jj, 2)
sn(j, 18) = sp(jj, 4)
sn(j, 19) = sp(jj, 5)

For jj = 2 To UBound(sp)
If sn(j, 1) = sp(jj, 16) Then exit For
Next
sn(j, 20) = sp(jj, 20)
sn(j, 21) = sp(jj, 21)

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), sf(jj - 22))
sn(j, jj) = Choose(jj - 21, sn(j, 5) * sn(j, 4), sn(j, 6) * sn(j, 4), sn(j, 7) * sn(j, 4), sn(j, 8) * sn(j, 4), sn(j, 9) / 600, sn(j, 10) / 60, sn(j, 11) / 60)
Next
Next

Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn
End Sub

[SamT]

?
For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next

You use that construct twice:
For jj = 2 To UBound(sp)
If sn(j, 1) = sp(jj, 16) Then exit For
Next



since it is so simple to read :rotflmao:
I had 4 questions about one line of your code. After reading the help files and rereading that line, (and some of the preceding ones,) many, many times, I answered all of them

Don't get me wrong, I've "stolen" more of your code than any one else's, but the first thing I do is Ctrl+H every variable.

[snb]

For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next
If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.
Als de waarde van kolom 3 in rij j van Array sn gelijk is aan de waarde van kolom in rij jj van Array sp, stop dan het zoeken.

[SamT]

If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.
Stop which loop?


Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then Exit For
MsgBox "Inner Loop # " & J
Next
MsgBox "Outer Loop # " & i
Next
End Sub

[snb]

For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit For
Next
I see only one loop.

If you know a method in VBA which exits an outer loop: please reveal/unveil your secret.... ;)

[SamT]

see only one loop.Me too. What does that loop do?


If you know a method in VBA which exits an outer loop: please reveal/unveil your secretGoTo

Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then GoTo AfterEndOfOuterLoop
Next
MsgBox "Outer Loop # " & i
Next
AfterEndOfOuterLoop:
End Sub

Counter > For To

Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then
i = 4
Exit For
End If
Next
Next
End Sub

[Svmaxcel]

Hi,
I removed the values from grey areas and ran code again.
However it was not completed.
VB only got values for 2 rows only, please check

[Svmaxcel]

Attaching 2 images for sample

[Svmaxcel]

One more thing to add.roster sheet which is created has repeated names and dates. So I would not like names to be repeated for same dates

[Paul_Hossler]

In my approach, it's only update rows in the selection that get updated.
Since you have so much data and it seems to be added daily, there did not seem to be a reason to re-compute thousands of lines of data. Just process the new data


I selected A2:A51 and ran the macro and all 2:51 gray areas were filled in

If you select A17 and run the macro, only row 17 is processes


If you really want to re-process all the data, that's easy to change

Deleted dups in roster also

[Sandler]

20346

At work, I use these settings on my Windows computer to speed things up. It's not exactly Excel help, but it will help the computer process things more efficiently.

[Svmaxcel]

Got it Buddy, it was cool and very informative.
I selected around 20000 rows and it calculated that in 30 sec.
I am OK with that.
In case I have to change any column name in Data sheet or rows in Mapping, it needs to be changed in VB also.
Can you tell me the line number for the same

[Svmaxcel]

Sub M_snb()
sn = Sheets("Data").Cells(1).CurrentRegion
sp = Sheets("Mapping").UsedRange
sf= split("dddd_'mmm-yy_\Wk ww_\WB dd-mm-yyyy_\WE dd-mm-yyyy","_")

For j = 2 To UBound(sn)
For jj = 2 To UBound(sp)
If sn(j, 3) = sp(jj, 1) Then Exit for
next
sn(j, 17) = sp(jj, 2)
sn(j, 18) = sp(jj, 4)
sn(j, 19) = sp(jj, 5)

For jj = 2 To UBound(sp)
If sn(j, 1) = sp(jj, 16) Then exit for
next
sn(j, 20) = sp(jj, 20)
sn(j, 21) = sp(jj, 21)

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), sf(jj - 21))
sn(j, jj) = Choose(jj - 21, sn(j, 5) * sn(j, 4), sn(j, 6) * sn(j, 4), sn(j, 7) * sn(j, 4), sn(j, 8) * sn(j, 4), sn(j, 9) / 600, sn(j, 10) / 60, sn(j, 11) / 60)
Next
Next

Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn
End Sub

The code didn't get through properly, I copied the code and got error variable or block variable not found.
While stepping it has a error on sn(j, 17) = sp(jj, 2)

[Svmaxcel]

Reports which I would be sending would be very confidential.
I wanted a (CONFIDENTIAL) Stamp with high transparency and little Slanted to be in between the report.
How can that be done.
I mean on several documents we have seen things like Confidential or Destroy after use.

[snb]

Why working with VBA when you haven't got a clue ?

[Svmaxcel]

You are Brain Child using your programming language skills, I am learning VBA and keep on learning by looking at codes.
I cannot be as smart as you in snb,
I removed Option Explicit

[snb]

Taking a course in the fundamentals of VBA would be very helpful.
VBA is a language in which we can't communicate if you are unfamiliar with the fundamentals.
Language skill are not hereditary but have to be acquired (at least I had to by studying books for instance, not by reading code; that comes much later)

[Svmaxcel]

Thanks for this.
I have already started the course and I am now on Do loop.

[snb]

:clap2:

[Svmaxcel]

:clap2:

Thanks a lot to Paul, SNB and Vbaexpress team.
The way you are helping others solve their problem in excel and climb up VBA ladder is appreciated.
Paul/Snb can you tell me which would be a better idea to go with I mean normal loop or arrays loop for the file attached earlier

[snb]

Apparently you have an empty column (column Q) in your file
You'd better remove that empty column.

You only have to adapt

Sub M_snb()
sn = Sheets("Data").Cells(1).CurrentRegion.resize(,28)

[Svmaxcel]

I got error Subscript out of range

[SamT]

I got error Subscript out of range
Well, that helps a lot. There's over twenty subscripts in that code.

How about showing the code you're testing with a comment to tell where the error is occurring?

[Paul_Hossler]

You are Brain Child using your programming language skills, I am learning VBA and keep on learning by looking at codes.
I cannot be as smart as you in snb,
I removed Option Explicit

There's been numerous 'discussions' about using Option Explicit and explicitly Dim-ing all variables with a specific Type when ever possible

Option Explicit tell VBA that all variable have to be Dim-ed and to me that helps eliminate silly errors

With out Option Explicit this will return nothing when you run the macro since 'asn' is NOT 'ans'


ans = 2 x A(1,2)
MsgBox asn


With Option Explicit that would generate an error that could be fixed before running the macro



Option Explicit

Dim ans as Long

ans = 2 x A(1,2)

MsgBox asn ' <<<<<< Compile time error

MsgBox ans ' <<<<<< OK




Same error proofing applies to always Dim-ing variables with the type that the are

It's easy to just Dim everything as Variant, or just leave off the 'As String' part, but if the macro is relying on a variable being a number and you accidently assign a string to it, many lines later


In 'One' the error doesn't show up until many likes after the bad input
In 'Two' the error shows up on the line that generated the error
In 'Three' the code works the first time, but fails the second time

To me, it's easier to debug a problem with Option Explicit and accurate Dim-ing. Maybe eventually I'll get so good I don't make errors like that but intel then, I like to use them



Option Explicit

Sub One()
Dim a, b, c As Variant

a = "asfsadf"
b = 123

'many. many, many lines

c = b * a ' run time Type mis-match error
MsgBox c
End Sub

Sub Two()
Dim a As Long, b As Long, c As Long

a = "asfsadf" ' run time Type mis-match error
b = 123

'many. many, many lines

c = b * a
MsgBox c
End Sub

Sub Three()
Dim a As Long, b As Long, c As Long

Range("A1").Value = 10
a = Range("A1").Value ' No run time Type mis-match error
b = 123

'many. many, many lines

c = b * a
MsgBox c

'later

Range("A1").Value = "asdfasdf"
a = Range("A1").Value ' run time Type mis-match error
b = 123

'many. many, many lines

c = b * a
MsgBox c
End Sub

[Paul_Hossler]

Reports which I would be sending would be very confidential.
I wanted a (CONFIDENTIAL) Stamp with high transparency and little Slanted to be in between the report.
How can that be done.
I mean on several documents we have seen things like Confidential or Destroy after use.

Excel doesn't have a Watermark capability (like MS Word).

The workaround is to use an image

In Help search for 'Add Watermark' - just in case, I added directions in the attachment

[snb]

I got error Subscript out of range
Your comment isn't helpful.

[Svmaxcel]

That was really helpful.
Now I understand better about Option Explicit, Dim-ing, Variants
Thanks a ton Paul.

[Svmaxcel]

In my approach, it's only update rows in the selection that get updated.
Since you have so much data and it seems to be added daily, there did not seem to be a reason to re-compute thousands of lines of data. Just process the new data


I selected A2:A51 and ran the macro and all 2:51 gray areas were filled in

If you select A17 and run the macro, only row 17 is processes


If you really want to re-process all the data, that's easy to change

Deleted dups in roster also

Can you also tell me the option to recompute entire sheet?

[SamT]

option to recompute entire sheet?
Select the entire sheet.

[Paul_Hossler]

Can you also tell me the option to recompute entire sheet?

I don't think Selecting the entire sheet will do it

Try




Worksheets("Sheet1").Calculate

[SamT]

I thought "re-compute" meant re-process and your code processed the Selection.

[Svmaxcel]

I mean that I don't want to select the range and excel should automatically check last used row and fill in the results

[SamT]

Let us call what Paul's code does a "Process," or "Processing" so we can avoid confusion.

Applicable Range is that Range that was Processed, and/or needs Processing. I think what you meant when you said, "Entire sheet."

As I understand, the Process converts formulas to values.

One can set up Paul's code to Process: A Selection; The entire applicable Range; Or only the unprocessed, (new,) Cells in the applicable Range.

Set Start = Range("A:A").Find(What:="=", LookIn:=xlFormulas)

[Svmaxcel]

Fabulous!!!
Marking as Solved