Excel Question

  steviegee 10:22 18 Feb 09

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.

  Picklefactory 11:30 18 Feb 09

What version of Excel are you using?

  steviegee 11:34 18 Feb 09


  Picklefactory 11:42 18 Feb 09

Pity you don't have 2007, it is a standard feature with that.
However, does this help?
click here

  VoG II 11:43 18 Feb 09

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

  Picklefactory 11:55 18 Feb 09

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
Set rngSort = Range(sStartCell, sEndCell)
For Each rng In rngSort
rng.Value = rng.Offset(0, 1).Interior.ColorIndex
Range(sStartCell).Sort Key1:=Range(sStartCell), _
Order1:=xlDescending, Header:=xlNo, _
End If

Application.ScreenUpdating = True
Set rngSort = Nothing
Exit Sub

MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly, "SortByColor"
Resume SortByColor_Exit
End Sub

  steviegee 11:57 18 Feb 09

Thankyou both for your help. VoGs worked a treat.

This thread is now locked and can not be replied to.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…