Delete columns based on variable criteria applied to variable row values

[Pigankle]

My First Macro-enabled Worksheet/VBA Project:

I have a table with seven header rows, 500 data rows and 1 header column. There will be roughly 40 data columns, although that may vary slightly. The four header rows contain a year and various summary statistics for the data in the corresponding column.

I want a function that has three arguments:
1. a sheetname (because I actually have several tables like the one I described above)
2. A criteria that will be used on a single row (e.g. " < 1954" or maybe ">4.5 AND <6")
2 A string that codes to the header row I want to apply the criteria to.

Here's what I have so far:

Public Sub DeleteColumnsFromWithOn(sheetname, strCriteria, rowType)

Dim xlCalc As XlCalculation

    On Error Resume Next

 'Get myself on the correct worksheet

     Application.Goto Reference:=Worksheets(sheetname).Range("A:BM"), Scroll:=True

' Use the rowType argument to determine which row to apply criteria to.    
    If rowType = "ptile" Then   rRow = 2
    ElseIf rowType = "year" Then   rRow = 4
    ElseIf (rowType = "fcast") Then rRow = 7
    Else: Exit Sub
    End If
    
'Some of the header rows are percentile values for other header rows.
' since I want to filter against percentiles of the whole collection, 
' I want to turn off calculations as I go (If I delete the column with the '
' largest 99th percentile value, I don't want to recalculate percentiles and 
' get a new matching column.

    'Turn off events and screen updating
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = True
    End With


' The actual filter/deletion process begins
'I wanted to use autofilter here, since all of the row-deleting examples I 
' could find use it, but it seems to only work for filtering out rows based on 
' column values, not the other way around.  Instead:

'I choose a number greater than the number of columns I will ever have
For c = 50 To 2 Step -1
  '******I THINK THIS IS THE STEP NEEDING HELP********
  ' I want to test with my criteria -  What is the syntax?
  If Cells(rRow, c) '****??????????
    Then Cells(rRow, c).EntireColumn.Delete
Next

'Revert back to old behavior
With Application
        .Calculation = xlCalc
        .EnableEvents = True
'        .ScreenUpdating = True
    End With
   On Error GoTo 0
End Sub

Is this basically a reasonable approach? If so, can anyone help me with applying my criteria to the value in the appropriate row as I loop through the columns?

Thanks,

Adam

 

[Pigankle]

After poking around, I think that BuildCriteria (the Access function) would be very helpful. Is there an equivalent for excel?

 

[Pigankle]

Hrmm...based on further reading, it sounds like this is a difficult issue. Perhaps it would be easier to transpose the whole spreadsheet, use autofilter, then transpose back. Is expression.transpose the most efficient method?