How to organize and insert blank cells between a huge number of data?

Kimberly · #1 09-24-2010, 09:30 AM

Code:

Sub FixList()
 
Range("A1").Select
 
   Do
    If (ActiveCell.Value > ActiveCell.Offset(0, 1)) Then
 
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
     Else:  ActiveCell.Offset(1, 0).Range("A1").Select 
 
        End If
 
       Loop Until IsEmpty(ActiveCell.Offset(0, 1))
 
    End Sub

Be sure to do this on a copy of your worksheet, not the original.

The macro will quit when it encounters a row where col B is empty.

tareq · #2 09-24-2010, 09:36 AM

Thank you Kim for your input, please can you tell me how to add this to excel??

tareq · #3 09-24-2010, 12:15 PM

Quote:

Originally Posted by Kimberly

Code:

Sub FixList()
 
Range("A1").Select
 
   Do
    If (ActiveCell.Value > ActiveCell.Offset(0, 1)) Then
 
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
     Else:  ActiveCell.Offset(1, 0).Range("A1").Select 
 
        End If
 
       Loop Until IsEmpty(ActiveCell.Offset(0, 1))
 
    End Sub

Be sure to do this on a copy of your worksheet, not the original.
The macro will quit when it encounters a row where col B is empty.

Well, thanks again, you did solve the problem, but I need more than that, attached is an example contains "Before" and "After" sheets that exactly describe the other problem I faced when applying your solution.
Mainly the code you posted didn't allow me to insert an entire row, and when I switch the columns the code didn't work. Please see the attached file and give me your feedback.
P.S. I also updated the example attached in the thread subject.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Bar chart help urgent ?????	aligahk06	Excel	1	07-08-2010 12:23 AM
Bar chart help urgent ?????	aligahk06	Project	0	07-03-2010 06:58 AM
Urgent help regarding automation.	aligahk06	Excel	1	01-14-2010 01:55 PM
Urgent Help Required	id12345	Word	4	12-29-2009 08:22 AM
Urgent help!	captainspi	Word	2	04-19-2009 11:17 AM