Looping too many rows (+500,000)

[Axel]

Hi, im working with to many rows (> 500,000) and several sheets or workbooks, using VBA which is the better and faster way to:

a) Loop rows in sheet 1 and add a value that i have to find in sheet 2 or other workbook. ( im using while ... loop with a match function to find the row of the value then with a specific column retrieve the value to add it )


b) Delete about 50,000 rows that match a criteria like empty cells in a columna A. (im using a filter and delete entire rows)

About 5 minutes to complete the process.. how can i reduce it?

Thanks!

 

[ISY]

hello

You should show the relevant code, then if you have not yet disabled events such as:

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

and restoring it at the end of the cycle:

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

 

[Axel]

Thanks for your reply ISY, i use your code but is almost the same time , the code im using:

a) To lookup and add a value

   i = 0
   Do While Cells(5 + i, 1).Value <> ""
     j = Application.WorksheetFunction.Match(Cells(5 + i, 5).Value, Range("rgColumnDist"), 0)
     Cells(5 + i, 19).Value = Sheets("Data").Cells(j, 11).Value
     i = i + 1
   Loop

b) To delete rows

   ActiveSheet.Range("$A$4:$Q$1000000").AutoFilter Field:=1, Criteria1:="="
   Range("B5").Select
   Do
      ActiveCell.Offset(1, 0).Select
   Loop Until Rows(ActiveCell.Row).Hidden = False
   Range(Selection, Selection.End(xlDown)).Select
   Selection.EntireRow.Delete

Thanks!