Delete Blank Cells without deleting columns (Super Hard)

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
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
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
just a question why if more than 8192 cell does the code have to be different
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
For the SpecialCells VBA function, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.
 

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top