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 Kilmer


"Joe 90" wrote in message
...
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

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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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

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")

--
Regards,
Bob Kilmer


"Joe 90" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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

Bob

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

Joe

"Bob Kilmer" wrote in message
...
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")

--
Regards,
Bob Kilmer


"Joe 90" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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

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 Kilmer


"Joe 90" wrote in message
...
Bob

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

Joe

"Bob Kilmer" wrote in message
...
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")

--
Regards,
Bob Kilmer


"Joe 90" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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

Joe 90 wrote:
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

"Bob Kilmer" wrote in message ...
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")

--
Regards,
Bob Kilmer


"Joe 90" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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

The example from the xl2002 vba Help:

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



jason wrote:

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

"Bob Kilmer" wrote in message ...
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")

--
Regards,
Bob Kilmer


"Joe 90" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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 Kilmer


"Joe 90" wrote in message
...
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







--

Dave Peterson