How to Filter Blank Cell on file that has almost 500K records?

[kuldeepjainesl]

Hello All, i have file with around 500K Records in worksheet. i want to filter coloum "D" for the "Blanks" Value but the filter dont show the "blank" as an option as coloum has all uniuqe alphanumeric values and filter only list first 9999 values to select. How to get rid form this situation?

my first preference will be using native feature of Excel however is it is limitation that VBA will be the only option to choose.

 

[kuldeepjainesl]

I could solve this for now by workaround helper coloum but I still looking for a solution.

What i did to achieve this i use helper coloum with =Len(D2) and this gave me the values "0" for blank and count of characters thereafter and i applied filter on "0" to find the "Blank"

 

[Somendra Misra]

@kuldeepjainesl

Hi,

Have you tried converting your data to Excel Table and than use Filter option?

Regards,

 

[Hui]

If this is not being done regularly I would do a Search/Replace on that field and replace the blanks with a value that will rank 1st in the Sort list eg: -1 or AAAA etc

 

[NARAYANK991]

Hi Kuldeep ,

You do not have to do anything.

If you just scroll to the end of the list of items being displayed , you will find the option (blanks) is listed.

Unless there are no blanks within the first 10000 unique items. In which case , you can always insert a blank row anywhere in your sheet.

Narayan

 

[kuldeepjainesl]

Both the Answer from Hui and Narayan is workable for me. Thanks