On 12 Jun., 14:39, Kieran H wrote:
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

Hi

Try this:

Set cell= .cells(1, ICol)

Regards,
Per