Application.Screenupdating not "always" working?

  • Thread starter Thread starter Joe 90
  • Start date Start date
J

Joe 90

Hi

A part of my macro clears the contents of a part of a row, selects a range
below it, cuts the range, and then pastes it to the row previously cleared,
and then repeats until all rows in the range are cleared. This is the only
part of the macro not "hidden" by Application.Screenupdating = false, so the
user will see all the activity. Is there a way to overcome this?

Thanks in advance

Joe
 
If you are using Select, that may be giving you trouble. One does not need
to use the Select method to act on Excel objects. You can also hide the
sheet, the workbook, the application window in VBA while processing. I
understand that you may not want to hide the entire app.window.
Application.Screen Updating usually works as advertised. I bet there is a
way to overcome this. How about posting a little code that illustrates the
problem?
 
Bob,


I can see why Select is a problem :) but have found that if I want the
current region selected, I have to use Select ?? Will post up some code to
help with the problem.

Joe
 
Joe,
I cannot make Select violate ScreenUpdating = False. Didn't think it should.
Not sure what is causing your trouble. I'll look at your code when you post
it, unless you figure it out first. <g>

As an aside, I use Select if I want the interface to show a selection, but
generally, using selecting in code is not necessary. Simply refering to the
objects creates faster, more concise code. For example the following code
was recorded:

Range("A1").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste

This is equivalent:

Range("A1").Copy Range("B2")
 
Bob

So can I "select" (in inverted commas!) a CurrentRegion without using
select?
(Hope that makes sense :) )

Joe
 
Well, yes, in a sense.

You can Copy a Range. A CurrentRegion is a Range. Therefore, you can Copy a
CurrentRegion.

You can Select a Range. A CurrentRegion is a Range. Therefore, you can
Select a CurrentRegion. A Selection is a Range. Therefore, you can Copy a
Selection.

It is no sin to use Select, but it is usually neater, more concise and often
faster. If you want to copy the range, you may as well copy the range
instead of selecting the range then copying the selection.

If you want to run a macro, say, that operates on the user selection, that
is a good time to use the Selection range. If you want to *show* a range of
cells as having been selected, use Select.
 
Bob,

My code calls all over the place to several procedures, so I have decided to
go through it line by line, seeking to remove "select" where I can. When I
find the culprit I'll let you know :) The only trouble being is that when
F8'ing through the code Application Screen Updating doesn't work at all lol

Joe
 
Bob,

Not using 'select' is a personal goal of mine at the moment
I use the copy argument like yourself detailed below - can this be
used with a pastespecial without selecting anything?

J
 
The example from the xl2002 vba Help:

With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With
 
Bob

Found my first problem:

Code: 
Worksheets("Sheet1").Select (or Activate)
[end code]

causes a breakout from Application.ScreenUpdating=false

So I am trying to reference the range I need using:

[code]
Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]

but I get an object error. I need to reference the range with .End because
the size of range starting at J2 will keep changing.

All/any help greatly appreciated

Joe
 
Joe said:
Bob

Found my first problem:
Code:
--------------------

Worksheets("Sheet1").Select (or Activate)
[end code]

causes a breakout from Application.ScreenUpdating=false

So I am trying to reference the range I need using:
Code:
--------------------

Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]

but I get an object error. I need to reference the range with .End because
the size of range starting at J2 will keep changing.

All/any help greatly appreciated

Joe

 >

Should it be this?
Code:
--------------------

Worksheets("Sheet1").Range("J2"*:* Range("J2").End(xlDown)). Copy
Worksheets("Sheet2").Range("b2")
[end code]


I think you need to have a colon to help select the range, not the comma that was there. I hope that works... I could sure use a formula that select ranges like that.
Click to expand...
 
Back
Top