Making spreadsheet rule

Question

I need to make a rule on spreadsheet such that if I put a strike through on any row it will auto-move the cell to the bottom of the sheet. Does anyone know how to do this?

Thanks

Answer 1

Changing the font style of a cell (example: setting strikethrough) does not trigger any events in Excel. There are two ways I would go about this.

Option 1: Use a custom function to detect if a cell(s) has a strikethrough and use sorting options to organize them. (Because calculation is not triggered by font styling, you would need to trigger calculation through other means.)

Option 2: Use a macro to identify which item to move and where to move it, which is much more complicated.

I've pasted my code from testing Option 1. The function can handle a one-column range (one cell to a column of cells) and is able to "Spill".

Public Function Has_Strikethrough(rng As Range)

Application.Volatile 'causes the function to update during every calculation event

Dim rng2() As Boolean 'allows the function to return an array

Dim it As Integer

ReDim rng2(rng.Rows.Count - 1) 'sets the return array to the same size as the source range

Dim Rowy As Double

Dim cl As Range

Dim Counter As Double

Counter = -1

For Each cl In rng

Counter = 1 + Counter 'advances the row in the array in time with the row from the source range

If cl.Font.Strikethrough = True Then   'tests the cells in the source range for strikethrough

    rng2(Counter) = True   'sets value to true if true

Else: rng2(Counter) = False   'sets value to false if false

End If 

Next cl

Has_Strikethrough = WorksheetFunction.Transpose(rng2) 'returns the array vertically

End Function

Answer 2

I have several rows there so I'm looking to auto-move rows that I strike through next to the last row.

Answer 3

Two notes.

1. This cannot be achieved through regular Excel. It will require VBA.
2. Can you define "bottom of the sheet"?