Speeding up a VBA Macro & Code Cleanup

[Nordicrx8]

Hey All, Happy Friday!

I have a macro that runs through a range of cells, looking for a True/False value. If the value is True, it hides the row.

Sub Hide()

StartRow = 14
EndRow = 157
ColNum = 12
For i = StartRow To EndRow
If Cells(i, ColNum).Value <> “TRUE” Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i

Dim c As Range
    For Each c In Range("U10:AL10").Cells
        If c.Value = "True" Then
            c.EntireColumn.Hidden = True
            Else
            c.EntireColumn.Hidden = False
        End If
    Next c

End Sub

The code works, but it takes a while for it to run - was curious if there was another way to accomplish the task, as it's used fairly frequently. The odd thing is, it used to run fairly quickly. About a day or two ago, it started to take much longer. Not sure if it's a glitch in the workbook, or if I made a change somewhere which is somehow causing it to take longer to run.

I also have a set of Macros to merge these cells. It runs through at an acceptable pace, but it's fairly messy. I tried putting them all on a single macro, but VBA didn't like it - threw errors at me. I had to break them up into multiple macros. If there's any way to clean or speed either of these up, I'd greatly appreciate any thoughts!

Thanks so much!

Sub Merge_MAT1()
 
    Range("O57:S57,O58:S58,O59:S59,O60:S60,O61:S61,O62:S62,O63:S63,O64:S64,O65:S65,O66:S66,O67:S67,O68:S68,O69:S69,O70:S70,O71:S71,O72:S72,O73:S73,O74:S74,O75:S75,O76:S76,O77:S77,O78:S78,O79:S79,O80:S80").Select

    Selection.Merge
    
    End Sub
    
Sub Merge_MAT2()

    Range("O81:S81,O82:S82,O83:S83,O84:S84,O85:S85,O86:S86,O87:S87,O88:S88,O89:S89,O90:S90,O91:S91,O92:S92,O93:S93,O94:S94,O95:S95,O96:S96,O97:S97,O98:S98,O99:S99,O100:S100,O101:S101,O102:S102,O103:S103,O104:S104,O105:S105,O106:S106,O107:S107").Select

    Selection.Merge
    
    End Sub
    
Sub Merge_MAT3()
    Range("O108:S108,O109:S109,O110:S110,O111:S111,O112:S112,O113:S113,O114:S114,O115:S115,O116:S116,O117:S117,O118:S118,O119:S119,O120:S120,O121:S121,O122:S122,O123:S123,O124:S124,O125:S125,O126:S126,O127:S127,O128:S128").Select
    
    Selection.Merge
    
    End Sub
    
    Sub Merge_MAT4()
    Range("O129:S129,O130:S130,O131:S131,O132:S132,O133:S133,O134:S134,O135:S135,O136:S136").Select

    Selection.Merge
    
    End Sub
    Sub Merge_MAT5()
         
    Range("O137:S137,O138:S138,O139:S139,O140:S140,O141:S141,O142:S142,O143:S143,O144:S144").Select
    
    Selection.Merge
    
    End Sub
    Sub Merge_MAT6()
        
    Range("O145:S145,O146:S146,O147:S147,O148:S148,O149:S149,O150:S150,O151:S151,O152:S152,O153:S153").Select
    
    Selection.Merge
    
End Sub
    Sub Merge_MAT7()
    
    Range("O154:S154,O155:S155,O156:S156,O157:S157").Select

    Selection.Merge
    
    End Sub

 

[JustJohn91]

I ran into a similar problem before when hiding rows. I found that by disabling screenupdating and turning of formula calculations the macro ran much quicker.
To disable both:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dont forget to revert them at the end of the macro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Try the below for merging your cells, it worked for me:

Sub MergeCells()
For R = 57 To 157
Range("O" & R & ":S" & R).Select
Selection.Merge
Next R

End Sub

 

[DanteAmor]

Try turning off screen updating.
And I made an adjustment to simplify the code:

Sub Hide_v1()
  Dim c As Range
  Dim StartRow As Long, EndRow As Long, ColNum As Long, i As Long
  
  Application.ScreenUpdating = False
  
  StartRow = 14
  EndRow = 157
  ColNum = 12
  For i = StartRow To EndRow
    Rows(i).Hidden = Cells(i, ColNum).Value <> "TRUE"
  Next i
  
  For Each c In Range("U10:AL10").Cells
    c.EntireColumn.Hidden = c.Value = "True"
  Next c

  Application.ScreenUpdating = True
End Sub

 

[Nordicrx8]

I ran into a similar problem before when hiding rows. I found that by disabling screenupdating and turning of formula calculations the macro ran much quicker.
To disable both:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dont forget to revert them at the end of the macro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Try the below for merging your cells, it worked for me:

Sub MergeCells()
For R = 57 To 157
Range("O" & R & ":S" & R).Select
Selection.Merge
Next R

End Sub

This fixed it - SO much faster! thank you! Also, new merge code worked perfectly - thanks so much!