Posted to microsoft.public.excel.misc
|
|
Help with VBA Code
You are spot on Bob, the worksheet was protected.
--
Jim
"Bob Phillips" wrote:
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)
"Jim G" wrote in message
...
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
--
Jim
|