Work sheet hangs - help appreciated

[Kieran H]

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

 

[Kieran H]

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

Some additional information:

If I replace my code and explicitly delete some of these columns I get
the following error :

The object invoked has disconnected from its clients

Replacement Code

Public Function DeleteColumns()
With Sheets("Data")
.Columns("AA:AF").Delete
.Columns("T:T").Delete
.Columns("D").Delete
.Columns("C:C").Delete
End With
End Function

 

[Don Guillett]

try this
Sub delcols()
For i = Range("ra_fy1").Columns.Count To 1 Step -1
Select Case LCase(Cells(1, i))
Case "b", "d": Columns(i).Delete 'MsgBox i
Case Else
End Select
Next i
End Sub

 

[Per Jessen]

Some additional information:

If I replace my code and explicitly delete some of these columns I get
the following error :

The object invoked has disconnected from its clients

Replacement Code

Public Function DeleteColumns()
With Sheets("Data")
  .Columns("AA:AF").Delete
  .Columns("T:T").Delete
  .Columns("D").Delete
  .Columns("C:C").Delete
End With
End Function- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi

To suppress the alert, add this line before deleting the columns,

Application.DisplayAlerts = False

Remember to set DisplayAlerts = True afterwards.

Regards,
Per

 

[Don Guillett]

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

 

[Kieran H]

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














- 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

 

[Don Guillett]

Hard to read when you bottom post and I TOP post. Pls TOP post for me.
Look in the vba help index for WITH


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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
in message














- 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