حذف ردیف های خالی در اکسل

Question

کریس یک کاربرگ دارد که در آن تعداد زیادی ردیف خالی در داده ها وجود دارد. او به دنبال یک ماکرو ساده است که تمام ردیف هایی را که کاملاً خالی هستند حذف می کند.

چند راه برای انجام این کار وجود دارد. بخش‌های زیر تمام ایده‌های مختلف برای خلاص شدن از شر آن ردیف‌های دردسرساز را بررسی می‌کنند.

انتخاب نقاط خالی

یکی از راه‌های سریع برای بررسی مکان‌های خالی این است که فقط موارد خالی را در داده‌های خود انتخاب کنید. این مراحل را دنبال کنید:

شکل 1. کادر گفتگوی Go To Special.

1. تمام داده هایی را که با آنها کار می کنید انتخاب کنید.
2. F5 را فشار دهید . اکسل کادر گفتگوی Go To را نمایش می دهد.
3. روی Special کلیک کنید. اکسل کادر محاوره ای Go To Special را نمایش می دهد. (شکل 1 را ببینید.)
4. دکمه رادیویی Blanks را انتخاب کنید.
5. روی OK کلیک کنید. اکسل سلول هایی را در محدوده شروع شما (مرحله 1) که خالی هستند انتخاب می کند.
6. تب Home نوار را نمایش دهید.
7. روی ابزار Delete کلیک کنید. اکسل ردیف ها را حذف می کند.

اخطار بزرگی در اتخاذ این رویکرد وجود دارد: هر ردیفی را که هر سلولی در آن خالی است حذف می کند. به عبارت دیگر، این روش در صورتی بهترین است که تنها جاهای خالی داده‌های شما در ردیف‌هایی باشد که واقعاً می‌خواهید حذف کنید. اگر تعدادی سلول خالی در میان داده‌هایی که واقعاً می‌خواهید نگه دارید وجود دارد، از این روش استفاده نکنید، زیرا آن ردیف‌ها را نیز حذف می‌کند.

مرتب سازی

اگر داده‌های شما از تعداد زیادی ردیف (مثلاً فقط ده ستون یا کمتر) تشکیل نشده است، ممکن است فقط داده‌ها را مرتب کنید. اگر هر ستون را در مشخصات مرتب‌سازی خود بگنجانید، در نهایت ردیف‌های خالی کاملاً در کنار یکدیگر قرار می‌گیرند و می‌توانید به راحتی آنها را حذف کنید.

اگر ستون‌های زیادی در داده‌های شما وجود دارد و مشکلی برای اضافه کردن یک ستون کمکی ندارید، می‌توانید با تغییر در روش مرتب‌سازی، از شر ردیف‌های خالی خلاص شوید. بیایید بگوییم که داده های شما در ستون A تا P است. در ستون Q یکی از این فرمول ها را وارد کنید:

=COUNTA(A1:P1)
=IF(COUNTBLANK(A1:P1)=16,"DELETE","")

مهم نیست از کدام یک استفاده می کنید. آنها هر دو اطلاعاتی را به شما می دهند که می توانید با آنها کار کنید. در مورد فرمول COUNTA، شما به سادگی باید بر اساس ستون Q مرتب کنید و هر چیزی را که دارای 0 در آن ستون است حذف کنید.

در مورد فرمول COUNTBLANK، شما می خواهید مقدار معادل (16) را به هر تعداد ستونی که تابع COUNTBLANK دارید تغییر دهید. (در این مورد، 16 تعداد ستون‌های محدوده A:P، شامل A:P است.) پس از اینکه فرمول در جای خود قرار گرفت، می‌توانید بر اساس ستون Q مرتب‌سازی کنید، سپس به سادگی تمام ردیف‌هایی را که کلمه «DELETE» در آن ستون دارند حذف کنید. .

تغییر دیگر در رویکرد مرتب‌سازی این است که به سادگی از قابلیت‌های فیلتر اکسل برای فیلتر کردن لیست خود استفاده کنید تا فقط در یکی از ستون‌های داده خالی باشد. (به عنوان مثال، شما ممکن است برای خالی های ستون A فیلتر کنید.) سپس می توانید ستون های باقی مانده را مرتب کنید تا کل ردیف های خالی را در یک مکان داشته باشید و سپس آن سطرها را حذف کنید.

ماکروها

اگر مرتباً نیاز به حذف ردیف های خالی دارید، باید به طور جدی استفاده از یک ماکرو را برای انجام کار سخت در نظر بگیرید. ماکرو شما به راحتی می تواند به نوار اضافه شود یا به یک کلید میانبر اختصاص داده شود تا بتوانید به راحتی آن را فراخوانی کنید.

قبلاً در این نکته اشاره کردم که در صورت تمایل می‌توانید از کادر محاوره‌ای Go To Special برای انتخاب سلول‌های خالی در محدوده داده‌های خود استفاده کنید و سپس ردیف‌هایی را که آن سلول‌های خالی روی آن‌ها قرار دارند را حذف کنید. شما می توانید همین کار را در یک ماکرو ساده انجام دهید:

Sub DeleteBlanks()
    Dim R As Range

    Set R = Range("A1:" & ActiveCell.SpecialCells(xlLastCell).Address)
    R.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

با این حال، به یاد داشته باشید که این ماکرو هر ردیفی را که در آن جای خالی وجود دارد، حذف می‌کند، حتی اگر بخواهید داده‌های دیگری را که ممکن است در آن ردیف باشد، نگه دارید.

یک رویکرد بهتر این است که بررسی جامع تری روی هر ردیف در کاربرگ انجام دهید:

Sub DeleteBlankRows()
    Dim lRows As Long
    Dim J As Long

    lRows = ActiveCell.SpecialCells(xlLastCell).Row
    Application.ScreenUpdating = False
    For J = lRows To 1 Step -1
        If WorksheetFunction.CountA(Rows(J)) = 0 Then Rows(J).Delete
    Next J
    Application.ScreenUpdating = True
End Sub

توجه داشته باشید که ماکرو آخرین سلول مورد استفاده در کتاب کار را تعیین می کند و مقدار lRows را برابر با ردیفی که آن سلول در آن قرار دارد تنظیم می کند. سپس یک حلقه For...Next از هر ردیف (از آخرین به اولین) عبور می کند و از تابع COUNTA برای تعیین اینکه آیا چیزی در آن سطر وجود دارد یا خیر استفاده می کند. اگر نه، ردیف حذف می شود.