How to speed up arrays in vba

M

mewashoo

New Member
Joined
Jul 19, 2017
Messages
10
Code:
Hello All

It's my first post on this forum, hello everyone!!!!

I was wondering if anyone know any way to speed up below code a little. It's exhausting RAM quite often especially if more than 1 spreadsheet I'd opened.

Code: 
Sub Recalculate()
'
' Recalculate Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlAutomatic

    'Clear content
    Range("C5:AP54").ClearContents
    Range("C5:AP54").ClearComments

    'C5 - Reactive Actions
    Range("C5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"
    Selection.AutoFill Destination:=Range("C5:C54"), Type:=xlFillDefault
    Range("C5:C54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'D5
    Range("D5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[2], MATCH(RC[-1],Sheet1!C[7],0)),"""")"
    Selection.AutoFill Destination:=Range("D5:D54"), Type:=xlFillDefault
    Range("D5:D54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'E6
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[4], MATCH(RC[-2],Sheet1!C[6],0)),"""")"
    Selection.AutoFill Destination:=Range("E5:E54"), Type:=xlFillDefault
    Range("E5:E54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'F6
    Range("F5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[60], MATCH(RC[-3],Sheet1!C[5],0)),"""")"
    Selection.AutoFill Destination:=Range("F5:F54"), Type:=xlFillDefault
    Range("F5:F54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'G5
    Range("G5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("G5:G54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'H5 - Planned Actions
    Range("H5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Extra Works"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-1])),11),"""")"
    Selection.AutoFill Destination:=Range("H5:H54"), Type:=xlFillDefault
    Range("H5:H54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'I5
    Range("I5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-3], MATCH(RC[-1],Sheet1!C[2],0)),"""")"
    Selection.AutoFill Destination:=Range("I5:I54"), Type:=xlFillDefault
    Range("I5:I54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'J5
    Range("J5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-1], MATCH(RC[-2],Sheet1!C[1],0)),"""")"
    Selection.AutoFill Destination:=Range("J5:J54"), Type:=xlFillDefault
    Range("J5:J54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'K5
    Range("K5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[55], MATCH(RC[-3],Sheet1!C,0)),"""")"
    Selection.AutoFill Destination:=Range("K5:K54"), Type:=xlFillDefault
    Range("K5:K54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'L5
    Range("L5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("L5:L54"), Type:=xlFillDefault

    'M5 - Extra Works Actions
    Range("M5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Planned"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-6])),11),"""")"
    Selection.AutoFill Destination:=Range("M5:M54"), Type:=xlFillDefault
    Range("M5:M54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'N5
    Range("N5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-8], MATCH(RC[-1],Sheet1!C[-3],0)),"""")"
    Selection.AutoFill Destination:=Range("N5:N54"), Type:=xlFillDefault
    Range("N5:N54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'O5
    Range("O5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-6], MATCH(RC[-2],Sheet1!C[-4],0)),"""")"
    Selection.AutoFill Destination:=Range("O5:O54"), Type:=xlFillDefault
    Range("O5:O54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'P5
    Range("P5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[50], MATCH(RC[-3],Sheet1!C[-5],0)),"""")"
    Selection.AutoFill Destination:=Range("P5:P53"), Type:=xlFillDefault
    Range("P5:P54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Q5
    Range("Q5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("Q5:Q54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'R5 - Ambers
    Range("R5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Amber"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-4]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-11])),11),"""")"
    Selection.AutoFill Destination:=Range("R5:R54"), Type:=xlFillDefault
    Range("R5:R54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'S5
    Range("S5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-13], MATCH(RC[-1],Sheet1!C[-8],0)),"""")"
    Selection.AutoFill Destination:=Range("S5:S54"), Type:=xlFillDefault
    Range("S5:S54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'T5
    Range("T5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-2],Sheet1!C[-9],0)),"""")"
    Selection.AutoFill Destination:=Range("T5:T54"), Type:=xlFillDefault
    Range("T5:T54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'U5
    Range("U5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[13], MATCH(RC[-3],Sheet1!C[-10],0)),"""")"
    Selection.AutoFill Destination:=Range("U5:U54"), Type:=xlFillDefault
    Range("U5:U54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'V5
    Range("V5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("V5:V54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'W5 - 3 Day Ambers
    Range("W5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""3 Day Ambers"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-9]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-16])),11),"""")"
    Selection.AutoFill Destination:=Range("W5:W54"), Type:=xlFillDefault
    Range("W5:W54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'X5
    Range("X5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-18], MATCH(RC[-1],Sheet1!C[-13],0)),"""")"
    Selection.AutoFill Destination:=Range("X5:X54"), Type:=xlFillDefault
    Range("X5:X54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Y5
    Range("Y5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-16], MATCH(RC[-2],Sheet1!C[-14],0)),"""")"
    Selection.AutoFill Destination:=Range("Y5:Y54"), Type:=xlFillDefault
    Range("Y5:Y54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Z5
    Range("Z5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[8], MATCH(RC[-3],Sheet1!C[-15],0)),"""")"
    Selection.AutoFill Destination:=Range("Z5:Z54"), Type:=xlFillDefault
    Range("Z5:Z54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AA5
    Range("AA5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AA5:AA54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AB5 - Red
    Range("AB5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Red"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-21])),11),"""")"
    Selection.AutoFill Destination:=Range("AB5:AB54"), Type:=xlFillDefault
    Range("AB5:AB54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AC5
    Range("AC5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-23], MATCH(RC[-1],Sheet1!C[-18],0)),"""")"
    Selection.AutoFill Destination:=Range("AC5:AC54"), Type:=xlFillDefault
    Range("AC5:AC54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AD5
    Range("AD5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-21], MATCH(RC[-2],Sheet1!C[-19],0)),"""")"
    Selection.AutoFill Destination:=Range("AD5:AD54"), Type:=xlFillDefault
    Range("AD5:AD54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    'AE5
    Range("AE5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[3], MATCH(RC[-3],Sheet1!C[-20],0)),"""")"
    Selection.AutoFill Destination:=Range("AE5:AE54"), Type:=xlFillDefault
    Range("AE5:AE54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AF5
    Range("AF5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AF5:AF54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AK5 - Freebar
    Range("AK5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C33=""FREE"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-30])),11),"""")"
    Selection.AutoFill Destination:=Range("AK5:AK54"), Type:=xlFillDefault
    Range("AK5:AK54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AL5
    Range("AL5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-32], MATCH(RC[-1],Sheet1!C[-27],0)),"""")"
    Selection.AutoFill Destination:=Range("AL5:AL54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AM5
    Range("AM5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-30], MATCH(RC[-2],Sheet1!C[-28],0)),"""")"
    Selection.AutoFill Destination:=Range("AM5:AM54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AO5
    Range("AO5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AO5:AO54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AP5 - STAT PPM
    Range("AP5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-41]:C[-31],SMALL(IF(Sheet1!C[-41]=Area,IF(Sheet1!C[-28]=""Planned"",IF((Sheet1!C[-27]=""Statutory""),IF((Sheet1!C[10]<Next),IF((Sheet1!C[10]>Last),ROW(Sheet1!C[-41])-MIN(ROW(Sheet1!C[-41]))+1,"""")))),""""),ROW(R[-4]C[-35])),11),"""")"
    Selection.AutoFill Destination:=Range("AP5:AP54"), Type:=xlFillDefault
    Range("AP5:AP54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AQ5
    Range("AQ5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-37], MATCH(RC[-1],Sheet1!C[-32],0)),"""")"
    Selection.AutoFill Destination:=Range("AQ5:AQ54"), Type:=xlFillDefault
    Range("AQ5:AQ54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AR5
    Range("AR5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-35], MATCH(RC[-2],Sheet1!C[-33],0)),"""")"
    Selection.AutoFill Destination:=Range("AR5:AR54"), Type:=xlFillDefault
    Range("AR5:AR54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AS5
    Range("AS5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-3],Sheet1!C[-34],0)),"""")"
    Selection.AutoFill Destination:=Range("AS5:AS54"), Type:=xlFillDefault
    Range("AS5:AS54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AT5
    Range("AT5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AT5:AT54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'borders
    Range("C5:F54, H5:K54, M5:P54, R5:U54, W5:Z54, AB5:AE54, AG5:AI54, AK5:AN54, AP5:AS54").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    'end
    Rows("5:54").Select
    Selection.RowHeight = 12
    Range("F5").Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
    MsgBox "All ok."

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
mewashoo, I'm no VBA Guru like many of the folks that are active on this Forum but several things jump out to me when I look at your code.

1. You have Calculation set to xlAutomatic. With that setting Excel is recalculating every time a single cell changes. I recommend changing that to xlManual and then within your code Enable it only when it is necessary for future actions within the code module. You can do this with these commands below.
<code>'recalculate all open workbooks
Application.Calculate

'recalculate a specific worksheet
Worksheets(1).Calculate

' recalculate a specific range </code>Worksheets(1).Columns(1).Calculate

2. You are using Range(x).Select to move around your spreadsheet. Through my own and many others questions and subsequent answers I have learned that efficient coding should never 'Activate' or 'Select'.
For example you have "Range("C5").Select. Select causes the cursor to actually move to that cell. Since VBA code is in control there is no need to move the cursor which takes time to occur. You write an Array Formula into that cell and then I assume you are wanting to copy that formula down to row 54.

You can actually combine 3 lines of code as follows:
Code: 
Range(Cells(5, 3), Cells(54, 3)).FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"

3. I don't understand what you are trying to accomplish with these lines of code:

Code: 
    Range("C5:C54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
You are selecting your Range, Copying it and then Pasting right back over what you copied which when I execute those cells go Blank. Maybe that is because I don't have all the other cells referenced in the Array Formula populated.

4. In your "With Selection" code you have .Value = .Value which is redundant and unnecessary.

Again, not an expert by a long shot but these changes, especially setting calculation to xlManual will speed things up for you.<code></code>
 
Upvote 0
frank_AL said:
mewashoo, I'm no VBA Guru like many of the folks that are active on this Forum but several things jump out to me when I look at your code.

1. You have Calculation set to xlAutomatic. With that setting Excel is recalculating every time a single cell changes. I recommend changing that to xlManual and then within your code Enable it only when it is necessary for future actions within the code module. You can do this with these commands below.
<code>'recalculate all open workbooks
Application.Calculate

'recalculate a specific worksheet
Worksheets(1).Calculate

' recalculate a specific range </code>Worksheets(1).Columns(1).Calculate

2. You are using Range(x).Select to move around your spreadsheet. Through my own and many others questions and subsequent answers I have learned that efficient coding should never 'Activate' or 'Select'.
For example you have "Range("C5").Select. Select causes the cursor to actually move to that cell. Since VBA code is in control there is no need to move the cursor which takes time to occur. You write an Array Formula into that cell and then I assume you are wanting to copy that formula down to row 54.

You can actually combine 3 lines of code as follows:
Code: 
Range(Cells(5, 3), Cells(54, 3)).FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"

3. I don't understand what you are trying to accomplish with these lines of code:

Code: 
    Range("C5:C54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
You are selecting your Range, Copying it and then Pasting right back over what you copied which when I execute those cells go Blank. Maybe that is because I don't have all the other cells referenced in the Array Formula populated.

4. In your "With Selection" code you have .Value = .Value which is redundant and unnecessary.

Again, not an expert by a long shot but these changes, especially setting calculation to xlManual will speed things up for you.<code></code>
Click to expand...





Hi frank_AL,

first of all thanks for replying.

Now, I have been trying to implement your ideas into my code more or less successfully. I need sheet to calculate automatically, however I have minimized loses by calculating ranges instead of sheet.
I removed all .Select and used your method instead, I wasn't aware that you can do that.
To answer 3rd question - I am trying to remove formulas from cells, but I decided to loop through the column and replace formula with value using .value = .value reference in foreach loop.

It did help, however using extra resource I have added some extra data on to spreadsheet, meaning more calculation. Now I am back to previous state.

Problem seems to always occure while executing
Code: 
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]    Range("A5").FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-41]:C[-31],SMALL(IF(Sheet1!C[-41]=Area,IF(Sheet1!C[-28]=""Planned"",IF((Sheet1!C[-27]=""Statutory""),IF((Sheet1!C[10][/FONT][/COLOR][/LEFT]<next),if((sheet1!c[10] style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; border-bottom-color: rgb(51, 51, 51); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(51, 51, 51); border-left-style: none; border-left-width: 0px; border-right-color: rgb(51, 51, 51); border-right-style: none; border-right-width: 0px; border-top-color: rgb(51, 51, 51); border-top-style: none; border-top-width: 0px; color: rgb(51, 51, 51); direction: ltr; font-family: monospace; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: pre; word-spacing: 0px;">Last),ROW(Sheet1!C[-41])-MIN(ROW(Sheet1!C[-41]))+1,"""")))),""""),ROW(R[-4]C[-35])),11),"""")"
    [LEFT][COLOR=#333333][FONT=monospace]Range("A5")[/FONT][/COLOR][/LEFT].AutoFill Destination:=Range("AP5:AP54"), Type:=xlFillDefault</next),if((sheet1!c[10]>
[LEFT][COLOR=#222222][FONT=Verdana]
Any ideas how could I simplify array with 5 variables?​
 
Upvote 0
Have you considered that it might actually be the formulas that are the problem?

More specifically the use of entire column references in the formulas.

Perhaps you should look at restricting the ranges used in to the formula to only rows with data.
 
Upvote 0
First of all I agree with Norie about the entire column references but how does the code below do?
Test it on a copy of your workbook in case I have anything wrong.

Rich (BB code): 
Sub Recalculate()
    '
    ' Recalculate Macro
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
'        .Calculation = xlManual
    End With
    'Clear content
    Range("C5:AP54").ClearContents
    Range("C5:AP54").ClearComments

    'C5 - Reactive Actions
    With Range("C5:C54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"
        .NumberFormat = "General"
        .Value = .Value
    End With


    'D5
    With Range("D5:D54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[2], MATCH(RC[-1],Sheet1!C[7],0)),"""")"
        .Value = .Value
    End With
    'E6
    With Range("E5:E54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[4], MATCH(RC[-2],Sheet1!C[6],0)),"""")"
        .Value = .Value
    End With

    'F6
    With Range("F5:F54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[60], MATCH(RC[-3],Sheet1!C[5],0)),"""")"
        .Value = .Value
    End With

    'G5
    Range("G5:G54").ClearContents

    'H5 - Planned Actions
    With Range("H5:H54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Extra Works"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-1])),11),"""")"
        .Value = .Value
    End With

    'I5
    With Range("I5:I54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-3], MATCH(RC[-1],Sheet1!C[2],0)),"""")"
        .Value = .Value
    End With


    'J5
    With Range("J5:J54")
        ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-1], MATCH(RC[-2],Sheet1!C[1],0)),"""")"
        .Value = .Value
    End With
    'K5
    With Range("K5:K54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[55], MATCH(RC[-3],Sheet1!C,0)),"""")"
        .Value = .Value
    End With
    'L5
    Range("L5:L54").ClearContents
    'M5 - Extra Works Actions
    With Range("M5:M54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Planned"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-6])),11),"""")"
        .Value = .Value
    End With
    'N5
    With Range("N5:N54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-8], MATCH(RC[-1],Sheet1!C[-3],0)),"""")"
        .Value = .Value
    End With

    'O5
    With Range("O5:O54")
        ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-6], MATCH(RC[-2],Sheet1!C[-4],0)),"""")"
        .Value = .Value
    End With

    'P5
    With Range("P5:P53")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[50], MATCH(RC[-3],Sheet1!C[-5],0)),"""")"
        .Value = .Value
    End With
    'Q5
    Range("Q5:Q54").ClearContents

    'R5 - Ambers
    With Range("R5:R54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Amber"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-4]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-11])),11),"""")"
        .Value = .Value
    End With

    'S5
    With Range("S5:S54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-13], MATCH(RC[-1],Sheet1!C[-8],0)),"""")"
        .Value = .Value
    End With
    'T5
    With Range("T5:T54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-2],Sheet1!C[-9],0)),"""")"
        .Value = .Value
    End With

    'U5
    With Range("U5:U54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[13], MATCH(RC[-3],Sheet1!C[-10],0)),"""")"
        .Value = .Value
    End With

    'V5
    Range("V5:V54").ClearContents

    'W5 - 3 Day Ambers
    With Range("W5:W54")
        Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""3 Day Ambers"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-9]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-16])),11),"""")"
        .Value = .Value
    End With


    'X5
    With Range("X5:X54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-18], MATCH(RC[-1],Sheet1!C[-13],0)),"""")"
        .Value = .Value
    End With

    'Y5
    With Range("Y5:Y54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-16], MATCH(RC[-2],Sheet1!C[-14],0)),"""")"
        .Value = .Value
    End With

    'Z5
    With Range("Z5:Z54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[8], MATCH(RC[-3],Sheet1!C[-15],0)),"""")"
        .Value = .Value
    End With

    'AA5
    Range("AA5:AA54").ClearContents

    'AB5 - Red
    With Range("AB5:AB54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Red"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-21])),11),"""")"
        .Value = .Value
    End With

    'AC5
    With Range("AC5:AC54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-23], MATCH(RC[-1],Sheet1!C[-18],0)),"""")"
        .Value = .Value
    End With

    'AD5
    With Range("AD5:AD54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-21], MATCH(RC[-2],Sheet1!C[-19],0)),"""")"
        .Value = .Value
    End With

    'AE5
    With Range("AE5:AE54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[3], MATCH(RC[-3],Sheet1!C[-20],0)),"""")"
        .Value = .Value
    End With

    'AF5
    Range("AF5:AF54").ClearContents

    'AK5 - Freebar
    With Range("AK5:AK54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C33=""FREE"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-30])),11),"""")"
        .Value = .Value
    End With

    'AL5
    Range("AL5:AL54").FormulaR1C1 = _
    "=IFERROR(INDEX(Sheet1!C[-32], MATCH(RC[-1],Sheet1!C[-27],0)),"""")"


    'AM5
    Range("AM5:AM54").FormulaR1C1 = _
    "=IFERROR(INDEX(Sheet1!C[-30], MATCH(RC[-2],Sheet1!C[-28],0)),"""")"

    'AO5
    Range("AO5:AO54").ClearContents


    'AP5 - STAT PPM
    With Range("AP5:AP54")
        .FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-41]:C[-31],SMALL(IF(Sheet1!C[-41]=Area,IF(Sheet1!C[-28]=""Planned"",IF((Sheet1!C[-27]=""Statutory""),IF((Sheet1!C[10]<next),if((sheet1!c[10]>Last),ROW(Sheet1!C[-41])-MIN(ROW(Sheet1!C[-41]))+1,"""")))),""""),ROW(R[-4]C[-35])),11),"""")"
        .Value = .Value
    End With

    'AQ5
    With Range("AQ5:AQ54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-37], MATCH(RC[-1],Sheet1!C[-32],0)),"""")"
        .Value = .Value
    End With

    'AR5
    With Range("AR5:AR54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-35], MATCH(RC[-2],Sheet1!C[-33],0)),"""")"
        .Value = .Value
    End With

    'AS5
    With Range("AS5:AS54")
        .FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-3],Sheet1!C[-34],0)),"""")"
        .Value = .Value
    End With

    'AT5
    Range("AT5:AT54").ClearContents

    'borders
    With Range("C5:F54, H5:K54, M5:P54, R5:U54, W5:Z54, AB5:AE54, AG5:AI54, AK5:AN54, AP5:AS54")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders.LineStyle = xlNone
        .BorderAround Weight:=xlThin
    End With


    'end
    Rows("5:54").RowHeight = 12

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .CutCopyMode = False
    End With
    MsgBox "All ok."

End Sub
 
Last edited:
Upvote 0
hi Mark,

Unfortunately I can't.it won't calculate correctly, 1 of variables is increasing by 1 each row.

Perhaps I should explain what I am trying to achieve.

I have a spreadsheet of data, roughly 20k rows.
I need to be able to import data based on specific set of filters in each column in 2nd sheet.

Below is one of arrays, perhaps I should reconsider redoing these arrays?

Code: 
{=IFERROR(INDEX(Sheet1!$A:$K,SMALL(IF(Sheet1!$N:$N="Extra Works",IF((Area=Sheet1!$A:$A),IF((Sheet1!$BD:$BD="Open Actions"),ROW(Sheet1!$A:$A)-MIN(ROW(Sheet1!$A:$A))+1,"")),""),ROW(G1)),11),"")}
 
Upvote 0
How are the array formulas connected to the filtering/importing?

Are you using them in 'helper' columns to help determine the data to import?
 
Upvote 0
Norie said:
How are the array formulas connected to the filtering/importing?

Are you using them in 'helper' columns to help determine the data to import?
Click to expand...


Actually, I'm not using any helper columns and I guess I have to.
Array is indexing through spreadsheet to find and return "job numbers" based on all "if" statements.

Code: 
{=IFERROR(INDEX(Sheet1!$A:$K,SMALL(IF(Sheet1!$N:$N="Extra Works",IF((Area=Sheet1!$A:$A),IF((Sheet1!$BD:$BD="Open Actions"),ROW(Sheet1!$A:$A)-MIN(ROW(Sheet1!$A:$A))+1,"")),""),ROW(G1)),11),"")}

Index has to go through A:K as it's indexing "job numbers" but only if match specific text in column A with field "area".
 
Upvote 0
almost certain it can be better, half way stop confirming that if you want everything in values

try it and say something

Code: 
Sub Recalculate()
'
' Recalculate Macro
'


'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual


    'Clear content
    Range("C5:AP54").ClearContents
    Range("C5:AP54").ClearComments


    With Range("C5:C54")
        .NumberFormat = "General"
        .Value = .Value
    End With
    With Range("H5:H54")
        .NumberFormat = "General"
    End With


    Range("C5:C54").FormulaArray = "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"
    Range("D5:D54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[2], MATCH(RC[-1],Sheet1!C[7],0)),"""")"
    Range("E5:E54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[4], MATCH(RC[-2],Sheet1!C[6],0)),"""")"
    Range("F5:F54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[60], MATCH(RC[-3],Sheet1!C[5],0)),"""")"
    Union(Range("G5:G54"), Range("L5:L54"), Range("Q5:Q54"), Range("V5:V54"), Range("AA5:AA54"), Range("AF5:AF54"), Range("AO5:AO54"), Range("AT5:AT54")).FormulaR1C1 = " "
    Range("H5:H54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Extra Works"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-1])),11),"""")"
    Range("I5:I54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-3], MATCH(RC[-1],Sheet1!C[2],0)),"""")"
    Range("J5:J54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-1], MATCH(RC[-2],Sheet1!C[1],0)),"""")"
    Range("K5:K54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[55], MATCH(RC[-3],Sheet1!C,0)),"""")"
    Range("M5:M54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Planned"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-6])),11),"""")"
    Range("N5:N54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-8], MATCH(RC[-1],Sheet1!C[-3],0)),"""")"
    Range("O5:O54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-6], MATCH(RC[-2],Sheet1!C[-4],0)),"""")"
    Range("P5:P54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[50], MATCH(RC[-3],Sheet1!C[-5],0)),"""")"
    Range("R5:R54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Amber"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-4]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-11])),11),"""")"
    Range("S5:S54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-13], MATCH(RC[-1],Sheet1!C[-8],0)),"""")"
    Range("T5:T54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-2],Sheet1!C[-9],0)),"""")"
    Range("U5:U54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[13], MATCH(RC[-3],Sheet1!C[-10],0)),"""")"
    Range("W5:W54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""3 Day Ambers"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-9]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-16])),11),"""")"
    Range("X5:X54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-18], MATCH(RC[-1],Sheet1!C[-13],0)),"""")"
    Range("Y5:Y54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-16], MATCH(RC[-2],Sheet1!C[-14],0)),"""")"
    Range("Z5:Z54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[8], MATCH(RC[-3],Sheet1!C[-15],0)),"""")"
    Range("AB5:AB54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Red"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-21])),11),"""")"
    Range("AC5:AC54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-23], MATCH(RC[-1],Sheet1!C[-18],0)),"""")"
    Range("AD5:AD54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-21], MATCH(RC[-2],Sheet1!C[-19],0)),"""")"
    Range("AE5:AE54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[3], MATCH(RC[-3],Sheet1!C[-20],0)),"""")"
    Range("AK5:AK54").FormulaArray = "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C33=""FREE"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-30])),11),"""")"
    Range("AL5:AL54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-32], MATCH(RC[-1],Sheet1!C[-27],0)),"""")"
    Range("AM5:AM54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-30], MATCH(RC[-2],Sheet1!C[-28],0)),"""")"
    Range("AP5:AP54").FormulaArray = "=IFERROR(INDEX(Sheet1!C[-41]:C[-31],SMALL(IF(Sheet1!C[-41]=Area,IF(Sheet1!C[-28]=""Planned"",IF((Sheet1!C[-27]=""Statutory""),IF((Sheet1!C[10]Last),ROW(Sheet1!C[-41])-MIN(ROW(Sheet1!C[-41]))+1,"""")))),""""),ROW(R[-4]C[-35])),11),"""")"
    Range("AQ5:AQ54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-37], MATCH(RC[-1],Sheet1!C[-32],0)),"""")"
    Range("AR5:AR54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-35], MATCH(RC[-2],Sheet1!C[-33],0)),"""")"
    Range("AS5:AS54").FormulaR1C1 = "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-3],Sheet1!C[-34],0)),"""")"
    
    Application.Calculate
    With Union(Range("C5:AF54"), Range("AK5:AM54"), Range("AO5:AT54"))
        .Value = .Value
    End With


    With Range("C5:F54, H5:K54, M5:P54, R5:U54, W5:Z54, AB5:AE54, AG5:AI54, AK5:AN54, AP5:AS54")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    
    Rows("5:54").Select
    Selection.RowHeight = 12
    Range("F5").Select
    
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
    MsgBox "All ok."


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
ActiveSheet. Paste error
Replies
1
Views
104
RoryA
RoryA
P
  • Question
Help with Vba Code
Replies
3
Views
215
Coyotex3
C
C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
117
myall_blues
myall_blues
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
360
kevin9999
K
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
132
CapRavOr
C

Forum statistics

Threads
1,216,126
Messages
6,129,007
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top