Dear Matthew,

Thankyou for this - the code works nicely to deactivate all the scrolling
and flickering in the backgroand and its much quicker too

All the best,

Roger

"Matthew Herbert" wrote:

Roger,

In general, using objects does not require you to activate a sheet. This is
because the object knows what it is (i.e. its identity) and where it belongs.
For example, "Dim Rng As Range" creates Rng as an object with a Range
identity. Rng then takes on all of the Range class attributes.
Additionally, the Range object has a general hierarchy of Workbook and
Worksheet. As a result, Rng.Parent is the same thing as referring to the
Worksheet object where Rng resides. Furthermore, Rng.Parent.Parent is the
same thing as referring to the Workbook object where Rng resides.

Another small, often overlooked, issue is that of "qualifying" your objects.
If you don't specify the Workbook/Worksheet hierarchy, then the
ActiveWorkbook/ActiveWorksheet gets appended to your Range object. So, "Set
Rng = Range("A81:E140")" refers to
ActiveWorkbook.ActiveWorksheet.Range("A81:E140"). If, for example, I wanted
to refer to Sheet2 and not the ActiveWorksheet (which we'll say is Sheet1),
then I would have to do something like "Set Rng =
Worksheets("Sheet2").Range("A81:E140")". Again, this appends ActiveWorkbook
to the object because no specific Workbook is specified.

If you do perform selections (which is almost completely unnecessary),
deletions, etc., then you can help the "speed" of things by doing at least
two things (1) set the calculation mode to Manual, and (2) turn off screen
updating. The code for this is below. I think that screen updating will
solve your problem.

I hope this helps.

Best,

Matthew Herbert

Sub Test()
Dim xlCalc As XlCalculation

With Application
'save off the current calculation mode
xlCalc = .Calculation
'set calculation to manual
.Calculation = xlCalculationManual
'turn off screen updating
.ScreenUpdating = False
End With

'reset the calculation mode
Application.Calculation = xlCalc

'in this example there is no need to reset ScreenUpdating
' because it returns to true each time the procedure finishes
End Sub

"Roger on Excel" wrote:

I utilize forms for running an excel spreadsheet.

I have a macro which deletes empty rows (of a specified range). I have it so
the macro works over several sheets.

The macro works fine, however it needs to activate and perform its actions
in each sheet in turn to select the rows and delete them.

I now activate the macro from a userform, and was wondering if there was a
way to execute the code such that the macro doesnt require each sheet to be
activated (ie visible) to work. It is quite distracting having the sheets
scrolling through in the background.

I tried to activate the code with the sheets hidden but the code doesnt work
when I do this.

i have attached the code I use for information. It seems somewhat ungainly
and I wondered if there was a better way to do this without having to have
the sheets visible. I eventually would like all the sheets hidden so the
user doesnt need to access them.


Public Sub DeleteRows()

Dim Rng As Range, Rng1 As Range

Set Rng = Range("A81:E140")

On Error Resume Next
Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete
End Sub

Can anyone help or advise me?

Thasnkyou, Roger