I have six macros like this


Sub FindxOgSletCostProdukt()
Dim nr As Long
Dim ncn As Integer

Application.ScreenUpdating = False
Sheets("CostProdukt").Activate

ncn = Sheets("CostProdukt").Range("iv3").End(xlToLeft).C olumn
nr = Sheets("CostProdukt").Range("a65536").End(xlUp).Ro w

For i = nr To 2 Step -1
If UCase(Range("a" & i)) = "X" Then
Range("a" & i).EntireRow.Delete shift:=xlUp
End If
Next i

For h = ncn To 2 Step -1
If UCase(Cells(3, h).Value) = "X" Then
Cells(3, h).EntireColumn.Delete shift:=xlRight
End If
Next h

Application.ScreenUpdating = True

End Sub

The macros are not quite identical as some of them only has the horisontal
part, other the vertical part and others both parts. Also the rownumber in
the Cells(3, h) is not the same, and the column a in If
UCase(Range("a" & i)) = "X" Then can also change.

All six macros has to be run from a command button. I do this with this
code:

Application.ScreenUpdating = False
Call FindxOgSletBeregninger
Call FindxOgSletAfsatteCosts
Call FindxOgSletAfsatteCBs
Call FindxOgSletCostProdukt
Call FindxOgSletCosts
Call FindxOgSletSimulering
Application.ScreenUpdating = True

My problem is, that the screen flimmers, Screepupdating = False is ignored.
I suppose its because of the Sheets(n).Activate. KIf the sheets are not
activated, no deletion takes place though. Is there a way to avoide the
flimmering.

Jan

It looks like in each of your six macros you do screenupdating = true at the
end, that'll cause a flicker. Remove that in each of your six macros, just
disable and reset in your main macro as you are already doing. The = False
line in each of the six macros is redundant

In passing it might be a good idea to include an error handler.

Regards,
Peter T


"Jan Kronsell" wrote in message
...
I have six macros like this


Sub FindxOgSletCostProdukt()
Dim nr As Long
Dim ncn As Integer

Application.ScreenUpdating = False
Sheets("CostProdukt").Activate

ncn = Sheets("CostProdukt").Range("iv3").End(xlToLeft).C olumn
nr = Sheets("CostProdukt").Range("a65536").End(xlUp).Ro w

For i = nr To 2 Step -1
If UCase(Range("a" & i)) = "X" Then
Range("a" & i).EntireRow.Delete shift:=xlUp
End If
Next i

For h = ncn To 2 Step -1
If UCase(Cells(3, h).Value) = "X" Then
Cells(3, h).EntireColumn.Delete shift:=xlRight
End If
Next h

Application.ScreenUpdating = True

End Sub

The macros are not quite identical as some of them only has the horisontal
part, other the vertical part and others both parts. Also the rownumber in
the Cells(3, h) is not the same, and the column a in If
UCase(Range("a" & i)) = "X" Then can also change.

All six macros has to be run from a command button. I do this with this
code:

Application.ScreenUpdating = False
Call FindxOgSletBeregninger
Call FindxOgSletAfsatteCosts
Call FindxOgSletAfsatteCBs
Call FindxOgSletCostProdukt
Call FindxOgSletCosts
Call FindxOgSletSimulering
Application.ScreenUpdating = True

My problem is, that the screen flimmers, Screepupdating = False is
ignored. I suppose its because of the Sheets(n).Activate. KIf the sheets
are not activated, no deletion takes place though. Is there a way to
avoide the flimmering.

Jan

You actually dont need to activate a sheet if you want to read, write,
delete any or all contents of the sheet.
I have made small changes to your code, have a look... I would say you
can still optimize the speed of this code execution and along with
that avoid screen flickering. I am sure this helps.

Sub FindxOgSletCostProdukt()
Dim nr As Long
Dim ncn As Integer


Application.ScreenUpdating = False

with Sheets("CostProdukt")
ncn = .Range("iv3").End(xlToLeft).Column
nr = .Range("a65536").End(xlUp).Row


For i = nr To 2 Step -1
If UCase(.Range("a" & i)) = "X" Then
.Range("a" & i).EntireRow.Delete shift:=xlUp
End If
Next i


For h = ncn To 2 Step -1
If UCase(.Cells(3, h).Value) = "X" Then
.Cells(3, h).EntireColumn.Delete shift:=xlRight
End If
Next h
end with

Application.ScreenUpdating = True


End Sub

Selva V Pasupathy
for more resources on visual basic and programming,
please visit : http://socko.wordpress.com

Thank you to both of you. Both solutions helped, but I went for Socko's
because of the reduction in time.

Jan

Jan Kronsell wrote:
I have six macros like this


Sub FindxOgSletCostProdukt()
Dim nr As Long
Dim ncn As Integer

Application.ScreenUpdating = False
Sheets("CostProdukt").Activate

ncn = Sheets("CostProdukt").Range("iv3").End(xlToLeft).C olumn
nr = Sheets("CostProdukt").Range("a65536").End(xlUp).Ro w

For i = nr To 2 Step -1
If UCase(Range("a" & i)) = "X" Then
Range("a" & i).EntireRow.Delete shift:=xlUp
End If
Next i

For h = ncn To 2 Step -1
If UCase(Cells(3, h).Value) = "X" Then
Cells(3, h).EntireColumn.Delete shift:=xlRight
End If
Next h

Application.ScreenUpdating = True

End Sub

The macros are not quite identical as some of them only has the
horisontal part, other the vertical part and others both parts. Also
the rownumber in the Cells(3, h) is not the same, and the column a in
If UCase(Range("a" & i)) = "X" Then can also change.

All six macros has to be run from a command button. I do this with
this code:

Application.ScreenUpdating = False
Call FindxOgSletBeregninger
Call FindxOgSletAfsatteCosts
Call FindxOgSletAfsatteCBs
Call FindxOgSletCostProdukt
Call FindxOgSletCosts
Call FindxOgSletSimulering
Application.ScreenUpdating = True

My problem is, that the screen flimmers, Screepupdating = False is
ignored. I suppose its because of the Sheets(n).Activate. KIf the
sheets are not activated, no deletion takes place though. Is there a
way to avoide the flimmering.

Jan