PDA

View Full Version : Solved: Sort Not Working When Formatting Applied



Opv
03-29-2010, 04:21 PM
Attached is a sample worksheet that includes two scripts. One of the scripts is for the Worksheet("Dictionary") and the other is a sort routine in its own Module.

Something (apparently unrelated to the range setting) in the "Worksheet(Dictionary") sheet is preventing the sort from including the first row of data in the sort routine. Whatever it is is also preventing me from cutting a row and pasting it to the end of the data rows.

As strange as it sounds (to me anyway), the problem appears to be in the temporary formatting that is applied to the range of data to highlight the cells for the active row. The reason I suggest this is that I can copy the data to another sheet, remove all the formatting and the sort seems to work fine.

Can someone please help me identify what it is causing the behavior described above?

Thanks,

Opv

Paul_Hossler
03-29-2010, 04:52 PM
I think your Selection_Change on Dictionary was the problem


I disabled Events around the sort, and a couple of other things.


Try it now



Option Explicit
Sub sortRows()
Dim myRows As Integer
Dim myCols As Integer
Dim myRange As Variant


Worksheets("Dictionary").Activate


'added
Application.EnableEvents = False


myRows = Range("A4", Range("A4").End(xlDown)).Count
myCols = Range("$A4:C4").Column


'changed Key1 to A4, and Header = xlNo (might be 2007 only)
Range("A4:C" & myRows + 3 & "").Sort Key1:=Range("A4"), Order1:=xlAscending, _
Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom


'added
Application.EnableEvents = True
End Sub



and



Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Data As Range
Dim i As Integer
Dim k As Long 'ActiveCell


i = 1
k = ActiveCell.Column()
Set Data = Range("A4:C10000")


If ActiveCell.Row <= 3 Then
Exit Sub
End If


'added
Application.ScreenUpdating = False


Data.Interior.ColorIndex = xlNone
Data.Font.Bold = False
Data.Font.ColorIndex = 0
Data.Font.Size = 11
Data.RowHeight = 13


With ActiveCell
.Offset(0, -(k - i)).Resize(1, 3).Interior.ColorIndex = 1
.Offset(0, -(k - 1)).Resize(1, 3).Font.Bold = True
.Offset(0, -(k - 1)).Resize(1, 3).Font.ColorIndex = 2
.Offset(0, -(k - 1)).Resize(1, 3).Font.Size = 14
.Offset(0, -(k - 1)).Resize(1, 3).RowHeight = 20
End With


'added
Application.ScreenUpdating = True


End Sub



By the way, the Selection_Change Target is the range that is now selected, so you could probably simplfy the code a bit


Paul

Opv
03-29-2010, 05:07 PM
I think your Selection_Change on Dictionary was the problem


I disabled Events around the sort, and a couple of other things.


Try it now
Paul

Thanks. That did the trick.

Opv