Macro to eliminate columns that fail specific criteria

[viralnerd2013]

Hello MrExcel users:

I need a macro that will hide or delete all columns in a data set where the variation within that column is less than 20. For example, the macro would turn this dataset (which is in the format I will be using):
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Pos1[/TD]
[TD]Pos2[/TD]
[TD]Pos3[/TD]
[TD]Pos4[/TD]
[TD]Pos5[/TD]
[TD]Pos6[/TD]
[TD]Pos7[/TD]
[TD]Pos8[/TD]
[TD]Pos9[/TD]
[TD]Pos10[/TD]
[/TR]
[TR]
[TD]time1[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]6.52[/TD]
[TD]0.00[/TD]
[TD]6.52[/TD]
[TD]0.00[/TD]
[TD]39.70[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]time2[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]3.08[/TD]
[TD]0.00[/TD]
[TD]4.08[/TD]
[TD]0.00[/TD]
[TD]13.60[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]time3[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]29.97[/TD]
[TD]0.00[/TD]
[TD]28.57[/TD]
[TD]0.00[/TD]
[TD]2.03[/TD]
[TD]7.14[/TD]
[TD]0.00[/TD]
[/TR]
</TBODY>[/TABLE]


Into this table:

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]Pos4[/TD]
[TD]Pos6[/TD]
[TD]Pos8[/TD]
[/TR]
[TR]
[TD]Time1[/TD]
[TD]6.52[/TD]
[TD]6.52[/TD]
[TD]39.70[/TD]
[/TR]
[TR]
[TD]Time2[/TD]
[TD]3.08[/TD]
[TD]4.08[/TD]
[TD]13.60[/TD]
[/TR]
[TR]
[TD]Time3[/TD]
[TD]29.97[/TD]
[TD]28.57[/TD]
[TD]2.03[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks for your help,
Eric

 

[viralnerd2013]

Greater than 20. Sorry for the confusion.

 

[Gary's Student]

Hi Eric:

How about:

Sub KolumnKiller()
Dim NLastColumn As Long, N As Long, rCol As Range
NLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
For N = NLastColumn To 2 Step -1
    Set rCol = Columns(N)
    If wf.Max(rCol) - wf.Min(rCol) < 20 Then
        rCol.EntireColumn.Delete
    End If
Next N
End Sub

 

[viralnerd2013]

Works beautifully! Thanks so much.