Delete Blank Cells without deleting columns (Super Hard)

[davidhall80]

I have blank cells between my data that I want to bring next to each other. I can't delete columns because there is data in the rows above. How do I delete the blank cells in columns A:E without deleting the entire column to bring my information together.
example



......A...............B...............C...............D...............E

1...yes...........yes..............no.............yes............no

2maybe..........no...............yes.............no............yes

3 no................................no..............................yes

4 no..................................................................no

5 yes...............................................yes..................


I want there to be no blank cells in the rows between any of this data
So row 3 would only go to column C and rows 4 & 5 would only go to column B. Make sense??? thanks in advance

 

[Tom Urtis]

To do this manually, just select the range, then from the worksheet menu click Edit > GoTo > Special > Blanks > OK.

Then with those blank cells selected, from the worksheet menu click on Edit > Delete > Shift cells left > OK.

You can do it all in one shot via VBA without looping if there are fewer than 8192 blank cells in used range of columns A:E.


Sub Test1()
Application.ScreenUpdating = 0
Dim LR&
LR = [A:E].Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
Range(Cells(1, 1), Cells(LR, 5)).SpecialCells(4).Delete xlToLeft
Err.Clear
Application.ScreenUpdating = 1
End Sub


If you have more than 8192 empty cells using VBA, this would do what you want:

Sub Test2()
Application.ScreenUpdating = 0
Dim LR&, x&
LR = [A:E].Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For x = 1 To LR
On Error Resume Next
Range(Cells(x, 1), Cells(x, 5)).SpecialCells(4).Delete xlToLeft
Err.Clear
Next x
Application.ScreenUpdating = 1
End Sub

 

[davidhall80]

thanks......amazing

 

[shippey121]

just a question why if more than 8192 cell does the code have to be different

 

[Tom Urtis]

For the SpecialCells VBA function, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.