Shift Cells Right if Blank - to Right Justify All Cells

[ktla111]

Hello --

I am looking for an efficient VBA solution to arrange the cells of my data so that all of the data is aligned to the right-most cell.

In the example below, the numbers are in column A and I would like those to stay in A and I would like all of the z's to be align in column M, the c's is Column L, the a's or b's in column K, etc while the remaining cells/columns are blank. (The alignment will be independent of the content in the cell - I have only used the same content for simplicity here.)



[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64"]c[/TD]
[TD="width: 64"]z[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD]z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD]z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD]c[/TD]
[TD]z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD]z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance!!

 

[Scott Huish]

What range is the data in now? Are there always a max of 3 values in each row (other than column A)? If not, how many would be the max values in each row?

 

[ktla111]

The actual data that I want to "right justify" is in columns E through BD. Each column could contain data - so 52 is the max number that could be shifted, but it will vary and it is unlikely that any rows have 52 columns populated. I want to keep A:D in those respective columns.

I can work around this by forcing certain cells to not be blank, but ideally, it is slightly more complicated. If AX is not blank, I want the data in AW through AZ shifted to BA:BD. If AT is not blank I want AS:AV shifted to AW:AZ. And so on, following the same pattern.