Compressing Spreadsheet

[MacIvor]

I want to automate the process of first finding a blank cell in a large(? 100x100) spreadsheet, and the EITHER deleting that cell and moving all the cells that are to its right in that row, one place left to fill in the gap , OR simply cutting and pasting the rest of the row on its right so that tha cell is now filled.

 

[Legacy 98055]

Hi Mac.
Run this on the active sheet.
<pre>
Sub ByeByeBlanks()
Dim c As Range, cCol As Integer, AllBlanks As Range
Set AllBlanks = Range("A1")
Application.ScreenUpdating = False
Do
cCol = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
If AllBlanks.Address = ActiveSheet.UsedRange.SpecialCells( _
xlBlanks).Address Then Exit Sub
Set AllBlanks = ActiveSheet.UsedRange.SpecialCells(xlBlanks)
For Each c In AllBlanks
c.Select
If c = "" Then _
Range(c.Offset(0, 1), Cells(c.Row, cCol)).Cut c
Next
Loop
Application.ScreenUpdating = True
End Sub

</pre>
Back up first. I only think it works.
Tom

 

[MacIvor]

Thanks Tom. I'll try it out and get back to you.