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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
For the SpecialCells VBA function, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.
 
Upvote 0

Forum statistics

Threads
1,217,449
Messages
6,136,696
Members
450,025
Latest member
Beginner52

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
Back
Top