Deleting Empty Rows using VBA code without the need to activate sh

[Roger on Excel]

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

 

[marcus]

Hi Roger

You did not specify which sheets you wanted the blanks in Col B
deleted. The following works on all sheets.

Take care

Marcus

Option Explicit
Sub DelBlankColB()

Dim lw As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
lw = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Range("B1:B" & lw).SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
Next ws

End Sub

 

[Chip Pearson]

The following code will delete all rows in all worksheets where the
cells A on each row are empty. If one or more cells in A on a row
have a value, that row is not deleted. Change the "A" to whatever
columns you want to test.

Sub AAA()
Dim WS As Worksheet
Dim R As Range
Dim N As Long
Dim LastRow As Long
Const CHECK_RANGE As String = "A" '<<<<<< CHANGE

For Each WS In ThisWorkbook.Worksheets
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For N = LastRow To 1 Step -1
If Application.CountA(.Range(CHECK_RANGE).Rows(N)) = 0
Then
.Rows(N).Delete
End If
Next N
End With
Next WS
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Matthew Herbert]

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]

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

 

[Roger on Excel]

Dear Marcus,

Thanks for replying,

The cells are B81:B140 in the following named sheets

St1,St2,St3,St4,St5.......St10

Would you be able to modify the code to cycle through these sheets for me?

Roger

 

[marcus]

Hi Roger

Here is the procedure which will work only on those sheets which start
with 'St' Adjust to suit. If you have sheets you don't want it to
work on which do start with St let us know.

Take care
Marcus


Option Explicit
Option Compare Text
Sub DelBlankColB()

Dim lw As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "St" Then
lw = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Range("B1:B" & lw).SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
End If
Next ws

End Sub

 

[Roger on Excel]

Many Thanks Marcus - Have a great week

Roger