I have a column of numbers with various numbers in. Some the of cells are coloured in. Is there a way I can sort them so that all the coloured cells are first or a formula that will delete all the non coloured cells? Thanks.
Assuming that it is column A and that bit is fill colour that you're interested in (not font colour) try
Sub DelCells() Dim LR As Long, i As Long LR = Range("A" & Rows.Count).End(xlUp).Row For i = LR To 1 Step -1 With Range("A" & i) If .Interior.ColorIndex = xlNone Then .Delete shift:=xlShiftUp End With Next i End Sub
Just noticed that the original code on the link sorts with coloured cells at the bottom, not top as you requested. If you want colours at the top it would be as below. But VoG™'s is simpler.
Sub SortByColor() On Error GoTo SortByColor_Err
Dim sRangeAddress As String Dim sStartCell As String Dim sEndCell As String Dim rngSort As Range Dim rng As Range
Application.ScreenUpdating = False
sStartCell = InputBox("Enter the cell address of the " & _ "top cell in the range to be sorted by color" & _ Chr(13) & "i.e. 'A1'", "Enter Cell Address")
If sStartCell > "" Then sEndCell = Range(sStartCell).End(xlDown).Address Range(sStartCell).EntireColumn.Insert Set rngSort = Range(sStartCell, sEndCell) For Each rng In rngSort rng.Value = rng.Offset(0, 1).Interior.ColorIndex Next Range(sStartCell).Sort Key1:=Range(sStartCell), _ Order1:=xlDescending, Header:=xlNo, _ Orientation:=xlTopToBottom Range(sStartCell).EntireColumn.Delete End If
SortByColor_Exit: Application.ScreenUpdating = True Set rngSort = Nothing Exit Sub
SortByColor_Err: MsgBox Err.Number & ": " & Err.Description, _ vbOKOnly, "SortByColor" Resume SortByColor_Exit End Sub