Need help with Sub DeleteUnused Macro

S

Steve

Hi,

I'm trying to run the subject macro from Debra Dalgliesh

at:
http://www.contextures.on.ca/xlfaqApp.html#Unused

However, everytime I try and run the macro it gets hung up at this
line of the macro ".Columns.Delete". I did the merged cell test and
there were no merged cells. I'm running Microsoft Excel 2003 if that
helps.


Please advise,

Steve



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
 
J

Jim Rech

That line of code should only be run if the sheet is completely empty of
cell contents. If that the case? The delete should work in any case,
unless the sheet is protected of course.

--
Jim
| Hi,
|
| I'm trying to run the subject macro from Debra Dalgliesh
|
| at:
| http://www.contextures.on.ca/xlfaqApp.html#Unused
|
| However, everytime I try and run the macro it gets hung up at this
| line of the macro ".Columns.Delete". I did the merged cell test and
| there were no merged cells. I'm running Microsoft Excel 2003 if that
| helps.
|
|
| Please advise,
|
| Steve
|
|
|
| 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
|
 
S

Steve

That line of code should only be run if the sheet is completely empty of
cell contents. If that the case? The delete should work in any case,
unless the sheet is protected of course.

--

| Hi,
|
| I'm trying to run the subject macro from Debra Dalgliesh
|
| at:
| http://www.contextures.on.ca/xlfaqApp.html#Unused
|
| However, everytime I try and run the macro it gets hung up at this
| line of the macro ".Columns.Delete". I did the merged cell test and
| there were no merged cells. I'm running Microsoft Excel 2003 if that
| helps.
|
|
| Please advise,
|
| Steve
|
|
|
| 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
|
Click to expand...

Jim,

Your right, that my first sheet was protected. It ran through after I
tried the code again on the unprotected sheet. I have one more
question though. Is there any way I can make this code work on
designated sheets only? When it ran on the entire workbook, all of my
formulas (on a couple of the worksheets) change the cell references to
"#REV".

Thanks for your help.


Regards,

Steve
 
D

Don Guillett

One way
Sub selectedsheets()
myarray = Array("xxx", "YYY")
For Each sh In myarray
MsgBox Sheets(sh).Range("a1")
Next
End Sub
 
J

Jim Rech

Fleshing out Don's idea of doing it by name:

Sub TestClearSheets()
ClearSheets Array("Sheet1", "Sheet2")
End Sub

Sub ClearSheets(ArrWS As Variant)
For Each wks In ActiveWorkbook.Sheets(ArrWS)
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
| > That line of code should only be run if the sheet is completely empty of
| > cell contents. If that the case? The delete should work in any case,
| > unless the sheet is protected of course.
| >
| > --
| >
| > | > | Hi,
| > |
| > | I'm trying to run the subject macro from Debra Dalgliesh
| > |
| > | at:
| > | http://www.contextures.on.ca/xlfaqApp.html#Unused
| > |
| > | However, everytime I try and run the macro it gets hung up at this
| > | line of the macro ".Columns.Delete". I did the merged cell test and
| > | there were no merged cells. I'm running Microsoft Excel 2003 if that
| > | helps.
| > |
| > |
| > | Please advise,
| > |
| > | Steve
| > |
| > |
| > |
| > | 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,
|
| Your right, that my first sheet was protected. It ran through after I
| tried the code again on the unprotected sheet. I have one more
| question though. Is there any way I can make this code work on
| designated sheets only? When it ran on the entire workbook, all of my
| formulas (on a couple of the worksheets) change the cell references to
| "#REV".
|
| Thanks for your help.
|
|
| Regards,
|
| Steve
|
 
S

Steve

Fleshing out Don's idea of doing it by name:

Sub TestClearSheets()
ClearSheets Array("Sheet1", "Sheet2")
End Sub

Sub ClearSheets(ArrWS As Variant)
For Each wks In ActiveWorkbook.Sheets(ArrWS)
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

--

| > That line of code should only be run if the sheet is completely empty of
| > cell contents. If that the case? The delete should work in any case,
| > unless the sheet is protected of course.
| >
| > --
| >
| >| > | Hi,
| > |
| > | I'm trying to run the subject macro from Debra Dalgliesh
| > |
| > | at:
| > | http://www.contextures.on.ca/xlfaqApp.html#Unused
| > |
| > | However, everytime I try and run the macro it gets hung up at this
| > | line of the macro ".Columns.Delete". I did the merged cell test and
| > | there were no merged cells. I'm running Microsoft Excel 2003 if that
| > | helps.
| > |
| > |
| > | Please advise,
| > |
| > | Steve
| > |
| > |
| > |
| > | 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,
|
| Your right, that my first sheet was protected. It ran through after I
| tried the code again on the unprotected sheet. I have one more
| question though. Is there any way I can make this code work on
| designated sheets only? When it ran on the entire workbook, all of my
| formulas (on a couple of the worksheets) change the cell references to
| "#REV".
|
| Thanks for your help.
|
|
| Regards,
|
| Steve
|
Click to expand...

thanks folks, got it working...Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with VBA Code 5
Sudden Increase in File Size 3
Delete cells but not charts 12
Problems with a method form multiple worksheets 1
Help to modify macro please 2
Named Range - Empty columns 8
operate macro across multiple sheets, error, cannot locate solutio 3
Reset Used Range, Debra Dalgliesh's code 2

Top