Remove all empty cell in a given range and shift cells Left

[S. Das]

I want an excel worksheet, which can delete all the blank cells in a given range and move all the cells left. I have attached a sample file for better understand. Sheet2A of the Sample file is the format of my main worksheet and but Sheet2A contains only 4 set of cell but main worksheet contains 30 set of cells.
Note : 1) This is a SAMPLE worksheet, as my main worksheet is very large in size can't upload here.
2) Range of cell (A3:AP1402)

 

[GraH - Guido]

Hello, S. Das

On the very small dataset on sheet 1 I could apply this (manual) procedure:
1. Select range A1:E6
2. On the home tab - section editing - option Find & Select -> choose Go To Special
3. select the radio option "Blanks" + OK

4. On the home tab - section cells - option Delete -> select Delete Cells then choose Shift Cells left

You can record a macro with these steps and tweak the code to fit your criteria.

However I have noticed you merged a lot of cells and then this does not work. You might need to reconsider these merged cells.

G.

 

[S. Das]

Thank you so much for your reply.
I need a automatic procedure or a macro. Secondly I am unable to reconsider the merged cells as so much formula is present there.

Is it possible to swap a data contained cell with a blank cell without removing blank cell.

I just want a sheet which looks like sheet2A, with all data contained cell in the left side for a particular room number.

 

[GraH - Guido]

Thank you so much for your reply.
I need a automatic procedure or a macro. Secondly I am unable to reconsider the merged cells as so much formula is present there.

Is it possible to swap a data contained cell with a blank cell without removing blank cell.

I just want a sheet which looks like sheet2A, with all data contained cell in the left side for a particular room number.

Hmmm, yes I understand the need for automation, hence a macro. I'm not an expert in VBA, but if you want to shift cells to the left and at the right you have merged cells, that won't work in Excel. According to me, a macro cannot solve that in a simple way. So I believe a clean worksheet lay-out avoids many issues. Perhaps looking at the problem differently is a good start. What if you could get the data like you want without doing the shift cells to the left? Way out of my league to start coding this.
So let's keep our eye open for a ninja, or expert, reply. They never cease to amaze me.