Max Excluding Strikethrough

[cjhardin]

Hi,

I have a data set where randomly things have strikethroughs in it due to errors in that data collection. The strikethroughs are put in manually. I'm trying to figure out how I could code a max of a set of 10 numbers excluding the numbers that have had a strikethrough applied to the set of numbers. I have over 100 data sets so that is why I'm trying to streamline this with a code. I've included an example below of what one of these sets of data would look like that I would need to pull information from.

 

[FormR]

Hi, I think you'd need to use a user defined function for that.

Here is one option for that.

Function max_no_strikethrough(r As Range) As Double
Dim c As Range
For Each c In r
    If Not c.Font.Strikethrough Then
        If IsNumeric(c.Value) Then
            If c.Value > max_no_strikethrough Then max_no_strikethrough = c.Value
        End If
    End If
Next c
End Function

To use, insert the code above into a standard code module in the VBA editor (ALT+F11 from Excel), save your workbook as an .XLSM file and use in the worksheet as below.

EDIT - the XL2BB mini-sheet doesn't show the strikethrough formatting, it has been applied to the noted rows in column C.

Book3
	B	C	D
1	21		22.5
2	21.8
3	22.5
4	18.9
5	27.5	this has strikethrough
6	38	this has strikethrough
7	21.8
8	20.6
9	26.6	this has strikethrough
10	17.9
Sheet1

Cell Formulas
Range	Formula
D1	D1	=max_no_strikethrough(B1:B10)

 

[cjhardin]

Hi, I think you'd need to use a user defined function for that.

Here is one option for that.

Function max_no_strikethrough(r As Range) As Double
Dim c As Range
For Each c In r
    If Not c.Font.Strikethrough Then
        If IsNumeric(c.Value) Then
            If c.Value > max_no_strikethrough Then max_no_strikethrough = c.Value
        End If
    End If
Next c
End Function

To use, insert the code above into a standard code module in the VBA editor (ALT+F11 from Excel), save your workbook as an .XLSM file and use in the worksheet as below.

Book3
	B	C	D
1	21		22.5
2	21.8
3	22.5
4	18.9
5	27.5	this has strike tthrough
6	38	this has strike tthrough
7	21.8
8	20.6
9	26.6	this has strike tthrough
10	17.9
Sheet1

Cell Formulas
Range	Formula
D1	D1	=max_no_strikethrough(B1:B10)

EDIT - the XL2BB mini-sheet doesn't show the strikethrough formatting, it has been applied to the noted rows in column C.

That has seemed to worked well with what I'm doing. I knew it would need a VBA related solution, thank you for your help!