Delete Columns based on a condition

[Joel Mills]

I would like to be able to delete columns in the Active Spreadsheed based on
cells in a Range (B2:IV2), where any cells containing #Value! or Grand
Total would result in the column being deleted. I am using Excel 2000.

Joel

 

[Bernie Deitrick]

Joel,

You could do a macro:

Sub DeleteColumns()
Dim i As Integer

For i = 256 To 1 Step -1
If Cells(1, i).Text = "#VALUE!" Or _
Cells(1, i).Text = "Grand Total" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Sub testerBBB()
Dim rng As Range, rng1 As Range
On Error Resume Next
Set rng = Range("B2:IV2").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireColumn.Delete
Do
Set rng1 = Range("B2:IV2").Find("Grand Total", _
Lookat:=xlPart)
If rng1 Is Nothing Then Exit Do
rng1.EntireColumn.Delete
Loop While True

End Sub

 

[Joel Mills]

Thanks Tom this work perfectly.

Sub testerBBB()
Dim rng As Range, rng1 As Range
On Error Resume Next
Set rng = Range("B2:IV2").SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireColumn.Delete
Do
Set rng1 = Range("B2:IV2").Find("Grand Total", _
Lookat:=xlPart)
If rng1 Is Nothing Then Exit Do
rng1.EntireColumn.Delete
Loop While True

End Sub

--
Regards,
Tom Ogilvy




based