Move data to empty left cell

**JawadAhmed** · 12-25-2012, 11:07 AM

I have a sheet with hundreds of rows and about 15 columns. Some of the cells are are empty cells and some are not. I want the data to move to left cells if empty so that all my data will be on the most left column and no empty cells remains in my data.

Can anyone give a reasonable solution as till now i select a range of data and drag it with mouse click to required cells. If this can be automated it will be awesome.

Regards.

**benishiryo** · 12-25-2012, 11:15 AM

hi JawadAhmed, welcome to the forum. for a non-VBA method, try:
Assuming your data starts from A2:O100
1. select A2.*
2. click & drag the scroll bar to the end where the data of column O ends (row 100)
3. press & hold the SHIFT key & left click on the last cell in column O (O100). you should see them all selected
4. press CTRL + G -> Special -> Blanks -> OK. now only blanks are selected
5. right-click on any of the selected blank cells & click "Delete" (you have to repeat the above steps if you select any other cells. another way which is safer would be to go to Home -> Delete -> Delete Cells)
6. Shift cells left
7. OK

**jeffreybrown** · 12-25-2012, 11:18 AM

Here is a macro solution if you are going to do this more than once...

Please Login or Register  to view this content.

**JawadAhmed** · 12-25-2012, 11:38 AM

Dear Benishiryo,

Your method worked liked a charm. I am really thankful for your help.

Dear Jeffreybrown,

I will also try your macro in the future. I need to do this procedure several times a week and this will save me tons of time.

Regards.

**jeffreybrown** · 12-25-2012, 11:46 AM

You are very welcome and we are glad to have helped.

**SargeOfNY** · 05-04-2020, 05:53 PM

Thank you for this tip. I had a table of ~9000 clients, each with one to six phone numbers, that I had to import into a new system that allowed only three. Since I had two home, two office and two other, I wanted to keep the first three I had, This worked. However, It shifted everything in each row left, so I had to copy the columns out to their own sheet, apply this fix, and copy and paste them back into the original sheet.