Delete "Empty" Cells in Excel

GhostViper

New Member
Joined
Jan 2, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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 ?
 
Upvote 0
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.

Code:
Sub t()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete
End Sub
 
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,739
Messages
6,180,674
Members
452,993
Latest member
FDARYABEE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top