Help with VBA Code

[Guest]

I obtained the code below from;

http://www.contextures.com/xlfaqApp.html#Unused, to whom I am very grateful.

However, in it's curent form it fails toward the end at : "
..Range(.Cells(myLastRow + 1, 1), _...".

If I remove
"For Each wks In ActiveWorkbook.Worksheets"

and replace "With wks" with "With ActiveSheet" it works fine (in some files
I point it to a particualr sheet first).

Can anyone inform me as to why this is?

Otherwise, it's solved many formatting problems I was having with shifting
data sizes.

Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub

 

[Bob Phillips]

What error do you get? Is the worksheet protected?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nick Hodge]

Jim

With wks...

Is using a worksheet object (you will notice Dim wks a Worksheet) to iterate
the entire worksheet collection (All the worksheets in the ActiveWorkbook).
This means anything between With wks... and Next wks... will be done to
every worksheet (Worksheet is specific from Sheets as it will ignore chart
sheets dialog sheets, code sheets, etc)

By activating a sheet in advance, as you intimate you are working only on
the active sheet. This really defeats the object of the for...next loop. you
could use

With ActiveSheet
'Do the stuff here
End with

With VBA you seldom need to activate or select anything and this is
demonstarted in Debra's code

Hope that sort of explains it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

 

[Guest]

You are spot on Bob, the worksheet was protected.

 

[Guest]

Thanks Nick, that and Bob's advice has explained everything. I reverted to
the original code adding "unprotect" to the beginning and "protect" at the
end.

Works exactly as I'd hoped.

Cheers

 

[Guest]

One thing I just noticed. When the unused rows are trimmed on a sheet that
is referenced by a formula that has a range large enough to cover max
possible range, the formula is trimmed to the last lowest range. EG
$A$1:$A$3000 becomes $A$1:$A$1321.

I'm using sumproduct so can't use references such as $A:$A etc.

Any solutions to this problem?

In the meantime, I've had to revert to restricting the code to the active
sheet.