Deleting Columns based on a certain criteria

[dcdw51]

I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.

Thank you

 

[Rick Rothstein]

I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.

But 1 is greater than 0.90 which you say you want deleted?

 

[dcdw51]

Let me clarify, all the columns have a "1" cell , so if the cells under the "1" cell have a value greater than 0.90 ( this does include 1) then they get deleted. But if there is just a "1" cell and values less than 0.90 and greater than -0.90, then the row doesnt get deleted.
Heres how the graph looks

1
0.8 1
0.21 0.54 1
0.32 0.22 0.3 1
0.78 0.32 0.92 0.43 1

The third column would get deleted in this case

 

[Rick Rothstein]

Let me clarify, all the columns have a "1" cell , so if the cells under the "1" cell have a value greater than 0.90 ( this does include 1) then they get deleted. But if there is just a "1" cell and values less than 0.90 and greater than -0.90, then the row doesnt get deleted.
Heres how the graph looks

1
0.8 1
0.21 0.54 1
0.32 0.22 0.3 1
0.78 0.32 0.92 0.43 1

The third column would get deleted in this case

Okay, to clarify...

When you said in your first message that "all the columns have a '1' the top of column", you did not mean the "1" was in Row 1, correct?

Also, the values being examined are those listed under the "1", correct?

Are all your values constants (that is, not formulas)?

Are all your data columns contiguous?

 

[dcdw51]

Yes, Yes, and Yes. Should of probably put the example in the description.
Thank you

 

[dcdw51]

Yes, Yes, Yes, and all the data in the columns are contiguous. Should of probably put the example in the description.
Thank you

 

[Rick Rothstein]

Yes, Yes, Yes, and all the data in the columns are contiguous. Should of probably put the example in the description.

Give this macro a try...

Sub DeleteColumnsTestCellsBelowTheOne()
  Dim X As Long, Z As Long, LastRow As Long, LastCol As Long, Ones As Range
  Application.ScreenUpdating = False
  LastCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Column
  For X = LastCol To 1 Step -1
    Set Ones = Columns(X).Find(1, Cells(Rows.Count, X), xlValues, xlWhole, , xlNext)
    If Not Ones Is Nothing Then
      LastRow = Cells(Rows.Count, X).End(xlUp).Row
      For Z = Ones.Row + 1 To LastRow
        If Cells(Z, X).Value <= -0.9 Or Cells(Z, X).Value >= 0.9 Then
          Ones.EntireColumn.Delete
          Exit For
        End If
      Next
    End If
  Next
  Application.ScreenUpdating = True
End Sub