Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm struggling with the attached so would appreciate any help.

    The code that I am currently using:

    Code:
    Dim w As Long, y As Long
    Dim x As Range
    
    	w = Cells(Rows.Count, 1).End(xlUp).Row
    	
    	Set x = Rows("2:" & w)
    	
    	x.Sort Key1:=Range("F2"), Order1:=xlAscending, _
    		OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    'Loop backwards through column A and insert rows if the value of the cell doesn't equal the value of the cell above.
    
    	For y = w To 3 Step -1
    		If Not Cells(y, 2) = Cells(y - 1, 2) Then
    			Cells(y, 2).Resize(2, 1).EntireRow.Insert
    			Cells(y, 2).Resize(2, 1).EntireRow.ClearFormats
    		End If
    	Next y
    I do not (ALWAYS) get the expected results, but sometimes I do.... (confused)

    Also, this is based on the values in A changing, I need it to be based on a change in the first 11 characters in F.

    Help please.

    [attachment=84367:Book5.xls]
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your present code doesn't look at column A but at column B.

    Does this loop do what you want?

    Code:
      For y = w To 3 Step -1
    	If Not Left(Cells(y, 6), 11) = Left(Cells(y - 1, 6), 11) Then
    	  With Cells(y, 2).Resize(2, 1).EntireRow
    		.Insert
    		.ClearFormats
    	  End With
    	End If
      Next y
    Note: the value in cell F12 is not consistent with that in P24.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, Thanks.

    Any idea why my code was occasionally missing a row?

    (my dummy data was wrong)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your code checks whether there is a change in column 2 = column B:

    If Not Cells(y, 2) = Cells(y - 1, 2) Then

    Column B changes less often than the first 11 characters of column F.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •