Excel Question

  steviegee 10:22 18 Feb 09
Locked

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

2003

  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
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

  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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

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

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…