Delete Blanks and Shift Cells Left with Current Selection

I have searched all over the forums for this. I have tried to select the range manually and use the 'Selection.' code below, which works just fine for a small amount of data, but does not work with several hundred cells selected. I also tried the 'Range' code below by manually defining the range of cells, but that did not work either.

Please Login or Register  to view this content.

I would be okay with a working macro that performed the blank cell delete and shift left functions with my current selection, but having a more dynamic macro would be even better.

Eventually I would like to create a macro that will count the number of rows and columns in which data is present to determine the range, rather than selecting it manually or modifying the macro for a preset range. That would make it easy to bind to a button so that I can copy various table sizes into the sheet and compress the data based on the number of rows and columns in which data is present.

I have attached small example of what I am trying to do.

Another option

Please Login or Register  to view this content.

Thanks both of you. I had some cells with blank text strings in them rather than actually being blank, which was causing the original code to fail.

AB33 - That code is excellent. I have used it to program a button for my sheet. Thanks a bunch!

Thank you very much. It worked fine.