Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please help, I'm stuck.....

    I need to set a range of rows 2: endxlup, sort by column 16, and insert 2 blank rows between each row where the data in column 16 changes.

    I am getting an error at: Set q1 = .Rows("2:" & w)

    Code:
    Dim w As Long
    
    	w = Cells(Rows.Count, 9).End(xlUp).Row
    	
    	Set q1 = .Rows("2:" & w)
    
    'sort
    
    'insert rows

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can only use .Rows within a block of the form

    With ...

    End With

    Otherwise it isn't clear what .Rows refers to. Does it work if you omit the . before Rows?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Update: Getting there, now I just need to be able to insert the rows in col16 where the data changes?

    Code:
    Dim w As Long
    Dim x As Range
    
    	w = Cells(Rows.Count, 8).End(xlUp).Row
    	
    	Set x = Rows("2:" & w)
    	
    	x.Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlGuess, _
    		OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can loop backwards through the cells in column P and insert rows if the value of the cell doesn't equal the value of the cell above.

    Code:
    Dim r As Long
    For r = w To 3 Step -1
      If Not Cells(r, 16) = Cells(r - 1, 16) Then
    	Cells(r, 16).Resize(2, 1).EntireRow.Insert
      End If
    Next r
    (The above is air code, I haven't actually tested it)

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works perfect Hans, Thanks. Except the inserted rows take on the formatting of the rows above or below. How can insert clean rows?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Below the line

    Cells(r, 16).Resize(2, 1).EntireRow.Insert

    insert

    Cells(r, 16).Resize(2, 1).EntireRow.ClearFormatting

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No: Object doesn't support this property or method.

    The problem with clearing the formatting afterwards is that I will end up with blocks of data with the upper and lowermost gridlines in each block being lost.

    How about cutting 2 clean rows from outside of the range and inserting them?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, it should have been ClearFormats instead of ClearFormatting.

    If you set up the borders correctly, ClearFormats should leave the borders of the rows above and below intact.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, Thanks, correct on both points.

    Finally, can I activate this specific code by double clicking cell A1? If so how?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think that's a good idea, but if you really want that:

    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Copy the following code into the worksheet module:

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Range("A1"), Target) Is Nothing Then
    	Application.EnableEvents = False
    	Cancel = True
    	Call YourMacro
    	Application.EnableEvents = True
      End If
    End Sub
    where YourMacro is the name of the macro that you wrote.

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775105' date='13-May-2009 22:11']I don't think that's a good idea, but if you really want that:[/quote]
    As a rule, I would not do this, but for this specific workbook, it seems the best approach. This will be used on all sheets in one specific workbook, so I need to add to all sheets? or can this be fired from this workbook?

    Thanks

    Added:

    To clarify, the code needs to be available to all sheets, but will only run on the active sheet at that time.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want the code for all sheets, don't put it in the individual worksheet modules, but in the ThisWorkbook module, and use the Workbook_SheetBeforeDoubleClick event:

    Code:
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
    	Application.EnableEvents = False
    	Cancel = True
    	Call YourMacro
    	Application.EnableEvents = True
      End If
    End Sub

  13. #13

Posting Permissions

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