Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VBA Code
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VBA Code
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VBA Code
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 DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "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 |
#4
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VBA Code
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 -- Jim "Nick Hodge" wrote: 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 DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VBA Code
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. -- Jim "Nick Hodge" wrote: 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 DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
Need 2 add second then third code with first code in the Tab View | Excel Worksheet Functions | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Write a code by code | Excel Discussion (Misc queries) |