deleting cells
Posted by Corey on June 26, 2000 12:54 PM
I have names as rows and phone numbers as columns. For example, Joe has three phone numbers, one in column A and one in column C. Jim has two; one in column B and one in column C. I need to delete the empty columns in each row and shift the phone numbers to the farthest left columns so that the phone numbers are next to each other closest to their name. Any help appreciated. Thanks.
Corey
Posted by Ryan on June 29, 0100 7:45 AM
Corey,
Where you see it says "For j = 2 To 10", change the 2 to a 5. This will start at column E (5) and go to J (10), if you need it to go farther then J, enter a bigger number then 10. So it should end up like this:
For j = 5 To 10. That should do it. Glad it works.
Ryan
Posted by Corey on June 28, 0100 3:26 PM
For j = 2 To 10 If Cells(i, j).Value = "" Then Cells(i, j).Delete Shift:=xlToLeft If Cells(i, j).Offset(0, 1).Value <> "" Then j = j - 1 End If Next j i = i + 1
worked great! Being an Excel novic, how would I start say at Column 5, row 2?
Posted by Ryan on June 26, 0100 4:45 PM
Corey,
Here is code that will take care of your needs. It starts in Row 2 and Column B and goes over 10 Columns. If you need more columns, you can change the 10. This code assumes that there are no blank rows between names. It was working fine for me. Hope it works for you. Let me know.
Ryan
Sub MoveNumbers()
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
i = 2
j = 2
Do While Cells(i, 1).Value <> ""
For j = 2 To 10
If Cells(i, j).Value = "" Then
Cells(i, j).Delete Shift:=xlToLeft
If Cells(i, j).Offset(0, 1).Value <> "" Then j = j - 1
End If
Next j
i = i + 1
Loop
Application.ScreenUpdating = True
End Sub
