On Jun 12, 2:23*pm, "Don Guillett" wrote:
Notice the dots in .range * .cells * .columns
Sub delcols()
With Sheets("Data")
For i = .Range("ra_fy1").Columns.Count To 1 Step -1
Select Case LCase(.Cells(1, i))
'Case "b", "d": MsgBox i
Case "b", "d": .Columns(i).Delete
Case Else
End Select
Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Kieran H" wrote in message

...





Hi,

XL2000 / WIN 2000

I am using the following code to remove unwanted columns from a
worksheet.

The worksheet is populated by serialized sp calls to a remote SQL
server. The regions are named as thay come in.

The code below checks the value in the column header of the first
named region
(steps through columns starting from the right hand side) and is
supposed to delete the entire column if a match is found.

Alas, what actually happens is the work sheet freezes.hangs - i.e I
cannot select a cell almost as if a modal form was open somewhere. and
I receive a message that the Method 'delete' of object range failed.

Info:

The worksheet is not locked
I have tried exporting all objects to new workbook
I have tried 'cleaning' project
Debug.Print Cell.Address will give the cell address I am expecting
There is no code running when work sheet is frozen
Named region definately exists
If I stop the code before the code below runs *- all is well *i.e. all
data correctly poulated & worksheet not frozen

Any help would be greatly aprreciated

Regards

Kieran

Public Function DeleteColumns()
Dim lCol As Long
Dim cell As Range

With Sheets("Data")
* For lCol = .Range("RA_FY1).Columns.Count To 1 Step -1
* Set cell = .Range("RA_FY1").Rows(1).Columns(lCol)
* * *Select Case LCase(cell.Value)
* * * * Case "groupcode", "areaofworkcode", "top" * '......etc
* * * * * *cell.EntireColumn.Delete Shift:=xlShiftToLeft
* * * * Case Else
* * *End Select
*Next
End With
Set Cell =Nothing
End Function- Hide quoted text -

- Show quoted text -

Don, Per

Many thanks

However, wouldn't .Cells in your example refer to the entire worksheet
and not the specific range
i.e it would deleter the Columns(i) but the index would refer to the
sheet not the range.

both methods should work assuming the correct column is referenced

I think the problem has more to do with the error message I got when I
tried to delete the rows explicitly

But I can't as work out what

Cheers

Kieran