Delete blank cells in single column and shift data in that column up

[vladlock]

Hey guys, if anyone can help me out with this I would really appreciate it. I have multiple columns of data, say B-G. Each column has a different number of rows worth of data in them. What I need to do is be able to delete a selection from a row (which I have figured out how to do) then I need to shift the data in that column up one so that there are no blanks between the data. The only command I have been able to find ends up deleting the entire row and thereby compromising the data in the other columns. Here is a copy of what I have so far for a single column.

Dim c As Range
Dim rng As Range
Dim i As Integer
If S4OB = True Then 'selects the list of names on a shift
For Each c In Range("b1:b100") 'finds the name in the shift and deletes it
If c = ComboBox1.Text Then Range("b" & c.row & ":b" & c.row).ClearContents
Next c
For i = 1 To 100 'finds the blank cell and shifts the data in just that column up
If ("b" & i) = "" Then
Range("b" & i).Select
Selection.Delete.shift.xlUp
End If
Next i
End If

 

[farmerscott]

Vladlock,

No VBA expert, when you are deleting it is best to start at the bottom and work your way up the column.

Dim c As Range
Dim rng As Range
Dim i As Integer

If S4OB = True Then                               'selects the list of names on a shift
For Each c In Range("b1:b100")                 'finds the name in the shift and deletes it
If c = ComboBox1.Text Then Range("B" & c).ClearContents
Next c
For i = 100 To 1 'finds the blank cell and shifts the data in just that column up
If ("b" & i) = "" Then
Range("b" & i).Delete.shift.xlUp
End If
Next i
End If

Hope that helps,

FarmerScott