VBA to delete cells from columns preceding the lowest value in the column

[mapa]

Dear All,
I've been passed a Macro to delete values after the highest within an array of values in a column. Now I'd like to do a similar operation, but deleting the values preceding the lowest.

The Macro I use for delete after the max is the following:

Sub deleteaftermax()
Dim i As Integer

For i = 1 To ActiveSheet.UsedRange.Columns.Count

If WorksheetFunction.Count(Columns(i)) > 0 Then

Range(Columns(i).Find(What:=WorksheetFunction.Max(Columns(i)), LookIn:=xlFormulas, LookAt:=xlWhole).Offset(3), _

Cells(Rows.Count, i).End(xlUp)).Clear

End If

Next

End Sub

Many thanks,
marco

 

[DanteAmor]

Hi and welcome to MrExcel!

Try:

Sub deleteafterMin()
  Dim i As Integer
  Dim f As Range
 
  For i = 1 To ActiveSheet.UsedRange.Columns.Count
    If WorksheetFunction.Count(Columns(i)) > 0 Then
      Set f = Columns(i).Find(WorksheetFunction.Min(Columns(i)), , xlFormulas, xlWhole)
      If Not f Is Nothing Then
        If f.Row > 3 Then
          Range(f.Offset(-3), Cells(1, i)).Clear
        End If
      End If
    End If
  Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

"Prefer not to say" for your Office version in your profile is not very helpful & I'm wondering why you wouldn't provide that information.
When people are trying to help you, it helps them to know what functions and features are available to you otherwise they may be wasting their time developing a suggestion that is not appropriate for you.

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

We do not know what is possible with your data, so is it possible that a column could have more than one equal maximum or minimum value?
Take the sample below. It has 4 equal maximum values (green). Your code would delete the values from 3 rows below the first maximum value as indicated in green in column B.
IF this sort of situation is possible, what do you want the code to do in relation to the minimum value (blue)?

• Do you want it to delete from the last minimum value as indicated in blue in column B, or
• Do you want it to delete from the first minimum value as indicated in blue in column C, or
• Something else?

Also, is it possible that a column could have one max and one min but the min is below the max in the column like column F below?
If so, what should happen?

mapa.xlsm
	A	B	C	D	E	F
1
2
3
4	8	Delete these?	Or delete these?
5	8	Delete these?	Or delete these?
6	6	Delete these?
7	12	Delete these?				8
8	5	Delete these?				8
9	11					8
10	8					13
11	5					8
12	6					8
13	11					8
14	15					8
15	13					8
16	15					1
17	14	These get deleted				8
18	15	These get deleted
19	14	These get deleted
20	15	These get deleted
Sample

 

[mapa]

@DanteAmor: It works, many thanks
@Peter_SSs: Sorry, I haven't specified the platform as I work on several releases, but if allowed, I'll update the profile to mention all of them. Concerning the type of data, I've experimental reads (typically 2k/sample) from which I need to extract the range of values corresponding to sigmoidal trend in the assay (performed on hundreds of samples). I've uploaded a picture to exemplify the process for one sample.

Many thanks again,
marco

 

[Peter_SSs]

but if allowed, I'll update the profile to mention all of them.

Yes, that is certainly allowed. However, if you do show/have multiple versions and/or platforms you need to specify when you ask a question which of those versions/platforms any suggestion has to work in.

Concerning the type of data, I've experimental reads (typically 2k/sample) from which I need to extract the range of values corresponding to sigmoidal trend in the assay (performed on hundreds of samples). I've uploaded a picture to exemplify the process for one sample.

That didn't address my question about whether there could be multiple maximums or minimums etc. However, if Dante's suggestion does what you want, that doesn't matter so no need to revisit those questions.

 

[DanteAmor]

@DanteAmor: It works, many thanks

Im glad to help you, thanks for the feedback