Delete "Empty" Cells in Excel

[GhostViper]

Hello Everyone,

I am trying to delete all the empty cells in my excel document, for sorting purposes. Everyday an excel document is generated with data from one of our systems. The system is capable of storing up to 20,000 values per day and inputs all the data into the file, even "blank" cells, until it reaches its 20,000 value cap. At the end of the month, all of this data needs to be compiled into a single report and ultimately needs to be sorted into a single column.

The 20,000 value limit is set into the programming and cannot be readily adjusted. The limit must remain high because there is a potential to reach close to that limit, so I cannot change that limit.

I have tried Find and Replace function to replace the blanks with a value and then replace the value with nothing, but after an hour of the function running, it has only processed 135,000 of the blanks. Correlated, it would take between 4-5 hours to process a months worth of data.

Is there a better way to process this?

Thank you,
GhostViper

 

[Michael M]

Are you trying to delete individual cells or entire rows ??
Are there more than one blank in a row ?
Can you post a small sample of the data ?

 

[JLGWhiz]

Before you use this, be aware that it will shift data from one column to another if cells are blank to the left. But it will quickly elimiate all blanks in the rows and move all data left where cells where blank It can leave your columns in an upside down waterfall configuration. Test it on a mock up.

Sub t()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete
End Sub

 

[GhostViper]

@Michael M I cannot post the data online. Each column contains 20,000 "values", but the number of actual values varies from 0-20,000 (0 values will never happen, less than 100 is extremely unlikely). All of these values are at the top of the column. After the actual values at the top of the column, it is all "blanks" until it reaches 20,000. Say there is 250 actual values for the day. There will be 250 values at the top of the column, and after the last actual value, there will be 19,750 "blanks" below it.

 

[JLGWhiz]

Now I am confused. If all your data is at the top of the sheet, with no blanks between rows, what is the point of deleting the blanks below? any sorting can be done with the blanks below the data being ignored. You need to create a mock up of what your data looks like then post a a screen shot of it or use the XL2BB add in to copy it to the thread so we can see it.

 

[GhostViper]

@JLGWhiz The excel document was wanting to consider the "blank" cells as not blank. That was the problem I was having. I should have explained more thoroughly. It was looking at the cells as having a value event though they did not. But, I have figured something out and so far it looks as though it works.

@Michael M @JLGWhiz Thank y'all for the help. Have a good day.

Thank you,
GhostViper

 

[JLGWhiz]

OK, I understand now. That sometimes happens when downloading from the web. It sends invisible characters and you have to figure out what they are and then delete them . They are not really blanks, but are non printing characters. thanks for the feed back.
regards, JLG

 

[Michael M]

@GhostViper If you put in the formula
=LEN("refer to any blank cell")....what result do you get ??
If you get a result greater than zero
Have a look at this function at Excel.tips
Remove non printing characters