Senin, Juni 20, 2011

Excel : Speed Up Calculation

Case : You want to delete bulk of rows (more than 50.000 rows) on excel
Solution : Use VBA
Script :

Sub delete_rows_zero()

Application.ScreenUpdating = False

Dim nMaxRow As Long, nrow As Long
nMaxRow = ActiveSheet.UsedRange.Rows.Count
For nrow = nMaxRow To 1 Step -1
      If Range("A" & nrow).Value = "delete" Then
      Range("A" & nrow).EntireRow.Delete
      End If
Next nrow

Application.ScreenUpdating = True

End Sub


Source : http://www.databison.com/index.php/how-to-speed-up-calculation-and-improve-performance-of-excel-and-vba/

0 comments: