מחיקת שורות ב-VBA באקסל: טיפים חשובים לשיפור יעילות

בעבודה עם גיליונות אקסל שמכילים כמויות גדולות של נתונים, עולה לא פעם הצורך למחוק שורות שעומדות בקריטריונים מסוימים, למשל – רשומות ריקות, שורות כפולות או נתונים לא רלוונטיים.
ברוב הפעמים תבצעו את המחיקות ידנית, אבל אם מדובר בפעולה שחוזרת על עצמה, הרבה יותר נכון להשתמש ב Power Query או ב-VBA כדי לייעל את התהליך.
ואם אתם רוצים להבין מתי משתמשים בכל אחת מהפלטפורמות, תוכלו לקרוא את המאמר הזה.

הנה שני טיפים חשובים, שיעזרו לכם לייעל את העבודה:

הטריק הראשון: מחיקה מהסוף להתחלה

כאשר משתמשים בלולאת VBA שעוברת על שורות, הטעות הנפוצה היא לעבור מהשורה הראשונה כלפי מטה ולמחוק תוך כדי. הבעיה היא שכל פעם ששורה נמחקת, המספור של השורות משתנה, וכך חלק מהשורות "קופצות" ומתפספסות.

ומה הפתרון? להתחיל מהשורה האחרונה ולעלות כלפי מעלה.

בצורה הזו, גם אם מחקנו שורה, האינדקסים שמעליה לא משתנים ולכן הלולאה נשארת יציבה.
הקוד הבא ימחק את השורות שאין בהן ערך בעמודה A מהסוף להתחלה:

For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Value = "" Then Rows(i).Delete   
Next i

מה בעצם עשינו פה?

עברנו על כל השורות החל מהשורה האחרונה, בדקנו אם היא ריקה (או כל בדיקה אחרת, בהתאם לצורך), ואם כן – היא נמחקת.
ריצה על לולאה מהסוף להתחלה מבטיחה שכל השורות ייבדקו ללא פספוס.

הטריק השני: שיפור ביצועים – מחיקה מרוכזת

כאשר יש אלפי שורות למחיקה, ביצוע פעולת Delete על כל שורה בנפרד מכביד מאוד על המחשב וגורם לאיטיות משמעותית.
השיטה היעילה יותר היא קודם לאתר את כל השורות שצריך למחוק, לאחד אותן ל־Range אחד, ואז למחוק בבת אחת. זה מקצר דרמטית את זמן הריצה.
הקוד הבא יאסוף את כל השורות שצריך למחוק למשתנה rngDelete , ואז ימחק הכל בבת אחת:

Set rngDelete = Nothing
LR = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LR
If Cells(i, 1).Value = "" Then   
If rngDelete Is Nothing Then      
Set rngDelete = Rows(i)         
Else      
Set rngDelete = Union(rngDelete, Rows(i))        
End If      
End If   
Next i

If Not rngDelete Is Nothing Then rngDelete.Delete

שימו לב שבקוד הזה אין צורך לרוץ מהסוף להתחלה, מכיוון שאנחנו מבצעים מחיקה אחת בלבד.

ולמה לא לעבוד עם Power Query?

אז קודם כל, אם אתם יכולים – תעשו את זה. זה הרבה יותר פשוט.
עם זאת, לא תמיד השימוש בו מתאים, למשל, קוד שכבר קיים, וצריך רק להוסיף את המחיקה, או כשרוצים גמישות גבוהה באוטומציה.

ואם נסכם, אז השיטה הראשונה פשוטה ואינטואיטיבית יותר, ואם אין לכם הרבה נתונים, תשתמשו בה.
אבל, אם יש לכם אלפי שורות, מחיקה מהסוף להתחלה תאט מאוד את קצב העבודה, ולכן נשתמש בשיטה השנייה של איסוף כל האינדקסים למשתנה אחד ומחיקה בבת אחת של כל השורות.

שאלות ותשובות בנושא מחיקת שורות ב-VBA

שאלה:
מדוע ביצוע לולאה רגילה מלמעלה למטה לצורך מחיקת שורות עלול לגרום לדילוג על נתונים?
תשובה:
כאשר שורה נמחקת, מספרי השורות שמתחתיה משתנים ו"קופצים" למעלה כדי למלא את החלל.
כתוצאה מכך, האינדקס של הלולאה ממשיך להתקדם כרגיל, אך הוא מדלג מעל השורה הבאה בתור,
שתפסה את מקומה של השורה שנמחקה, מה שמוביל לפספוס רשומות.

שאלה:
כיצד שיטת איסוף הטווחים (Union) מסייעת בשיפור ביצועים בעת מחיקת אלפי שורות?
תשובה:
ביצוע פעולת מחיקה (Delete) נפרדת עבור כל שורה ושורה מכביד מאוד על משאבי המחשב וגורם לאיטיות.
שיטת האיסוף מאתרת תחילה את כל השורות המיועדות למחיקה, מאחדת אותן לטווח נתונים אחד מרוכז, ומבצעת את פעולת המחיקה פעם אחת בלבד על הכל יחד.

שאלה:
מתי מומלץ להשתמש בשיטת הלולאה ההפוכה (מהסוף להתחלה) על פני שיטות מורכבות יותר?
תשובה:
שיטת הלולאה ההפוכה נחשבת לפתרון פשוט ואינטואיטיבי ליישום המונע שגיאות אינדקס.
השימוש בה מומלץ כאשר בסיס הנתונים אינו גדול במיוחד, כך שמהירות הריצה אינה הגורם הקריטי ביותר, והעדיפות היא לכתיבת קוד ברור וקל להבנה.