delete blank cells, shift populated cells right

[BonnieM]

I have a very large spreadsheet with rows of data containing blank cells in the middle of a row.

I need to delete the blank cells within a range and shift the populated cells to the right.

I'm a vba beginner.

Tried this in "this workbook":

Sub Blanks()
Range("A1:x9").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlRight
End Sub

but got an error "X 400" which I can't even find in the help screens!

Can anybody be a hero here?

 

[BonnieM]

ok, I cut it from This Workbook and pasted into Module 1, changed the range to be c2...x30, still nothing happened

 

[lrobbo314]

Read the post I just put up. The "This Workbook" thing is not where you need to paste the code. I laid out the instructions on how to paste the code. You will also have to change the range to whatever your range with data is. so if it's A1:AC50,000, then you will have to change the code to reflect that range.

 

[BonnieM]

We have a time lag. I did as you suggested, and this time put the full range in, nothing appeared to change

 

[lrobbo314]

Can you post the code that you have in module1 now?

 

[BonnieM]

Sure:

Sub test()
On Error GoTo errH
Dim r As Range
Dim b As Range
Dim x As Integer


Set r = Range("c2:x51565")


For x = r.Cells.Count To 1 Step -1
If r(x).Address = "$S$1" Then
Debug.Print
End If
If r(x).Column() <> 1 Then
If r(x) = "" Then
Debug.Print r(x).Address
Set b = Range(r(x), r(x).End(xlToLeft).Offset(, 1))
b = b.Offset(, -b.Cells.Count).Value
b.Offset(, -b.Cells.Count) = vbNullString
End If
End If
Next x


errH:
If x = 0 Then Exit Sub
Set b = r(x).End(xlToLeft)
r(x) = b.Value
b.Value = vbNullString
Resume Next


End Sub

 

[lrobbo314]

Try changing x from an integer to Long.

So, Dim x as long

instead of

Dim x as integer

 

[BonnieM]

nope

 

[lrobbo314]

I don't know what to tell you. It is working on this end.

Can you step through the code by tapping the F8 key in the VB editor and try to figure out what is going on.

You'll see it highlight rows one by one as it executes the code. Tell me if it is skipping to the line that says errH:

 

[BonnieM]

I've attempted this F8 process now several times. While I see the highlighting changing on the code page, nothing appears to be happening on the actual data spreadsheet. (I have 2 monitors and I have the developer tab on one and the data sheet on the other.)

Do I need to do something special to see this working on the data?

 

[BonnieM]

I figured part of it out. The cells are not truly blank, they just look blank. They have a space in them. So I edited the code to change the "" to " " as shown below and adjusted the range to 40 rows for testing purposes.

I also figured out that for some reason it is starting from the bottom so that why I couldn't see what was happening with the F8 thing.

It seems we are now close, but:

1.it actually deleted the populated cells in column a completely - it appears the data from column A goes into column x and then the data from column b overwrites it.
2.it is reversing the order of the populated cells in columns c, d, etc. as it moves them - I need them just to shift right in the same order.

I do appreciate your help - I'm trying to understand the logic to be able to do this myself, but i am baffled!

Sub test()On Error GoTo errH
Dim r As Range
Dim b As Range
Dim x As Long


Set r = Range("c2:x40")


For x = r.Cells.Count To 1 Step -1
If r(x).Address = "$S$1" Then
Debug.Print
End If
If r(x).Column() <> 1 Then
If r(x) = " " Then
Debug.Print r(x).Address
Set b = Range(r(x), r(x).End(xlToLeft).Offset(, 1))
b = b.Offset(, -b.Cells.Count).Value
b.Offset(, -b.Cells.Count) = vbNullString
End If
End If
Next x


errH:
If x = 0 Then Exit Sub
Set b = r(x).End(xlToLeft)
r(x) = b.Value
b.Value = vbNullString
Resume Next


End Sub