How to remove blank cels

[Hook]

I'm sure this has been brought up before, but I need to remove blank cells from columns and
consolidate the remaining ones at the top. The attachment has is an example of the unfixed
columns.

Thanks as always.

[jraj1106]

Hi

Try this code:

Sub delblanks()
Dim r As Long, cnt As Integer

r = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
cnt = 1

For i = 2 To r
If Sheets("sheet1").Range("A" & i).Value = "" And Sheets("sheet1").Range("B" & i).Value = "" Then
Sheets("sheet1").Rows(i & ":" & i).Delete
i = i - 1
cnt = cnt + 1
End If
If cnt > r Then
Exit For
End If
Next
End Sub

Regards

[Hook]

Thanks for the code. However, I'm not sophisticated enough to know how to use it.
Would you please be so kind as to enlighten me.

Hook

[tigeravatar]

Hook,

Looking at your example file, why don't you just use a sort on LLNAME? Here's how:

1. Select columns A and B
2. Go to the Data tab on the ribbon
3. Click the big Sort button
4. Check "My data has headers"
5. In the Sort By, select LLName
6. Click OK

Attached are screenshots showing the instruction steps and sort results.

[Hook]

Hey tigeravatar,

That's way too simple. I wanted something high powered and complicated.

And am I ever embarrased.

Thanks much.

Hook

[tigeravatar]

You're very welcome