Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been
removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
Thanks for the reply Don,
But i do have SOME shapes in the sheet that i do NOT want deleted. The names of these begin with S or B (As per the code) Corey.... "Don Guillett" wrote in message ... Try this to CUT(delete) all shapes on the active sheet Sub ShapesCut() For Each s In ActiveSheet.Shapes s.Cut Next End Sub -- Don Guillett SalesAid Software "Corey" wrote in message ... I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
Option Explicit
Sub ShapesCut() dim S as shape For Each s In ActiveSheet.Shapes if lcase(s.name) = "s" _ or lcase(s.name) = "b" then 'keep it else s.delete 'or .cut?? end if Next s End Sub (Untested. Uncompiled. Watch for typos.) Corey wrote: Thanks for the reply Don, But i do have SOME shapes in the sheet that i do NOT want deleted. The names of these begin with S or B (As per the code) Corey.... "Don Guillett" wrote in message ... Try this to CUT(delete) all shapes on the active sheet Sub ShapesCut() For Each s In ActiveSheet.Shapes s.Cut Next End Sub -- Don Guillett SalesAid Software "Corey" wrote in message ... I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
Doh. I forgot the left() bits.
Option Explicit Sub ShapesCut() dim S as shape For Each s In ActiveSheet.Shapes if lcase(left(s.name,1)) = "s" _ or lcase(left(s.name,1)) = "b" then 'keep it else s.delete 'or .cut?? end if Next s End Sub (Still untested!) Don Guillett wrote: Try this to CUT(delete) all shapes on the active sheet Sub ShapesCut() For Each s In ActiveSheet.Shapes s.Cut Next End Sub -- Don Guillett SalesAid Software "Corey" wrote in message ... I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
Friday
-- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... Doh. I forgot the left() bits. Option Explicit Sub ShapesCut() dim S as shape For Each s In ActiveSheet.Shapes if lcase(left(s.name,1)) = "s" _ or lcase(left(s.name,1)) = "b" then 'keep it else s.delete 'or .cut?? end if Next s End Sub (Still untested!) Don Guillett wrote: Try this to CUT(delete) all shapes on the active sheet Sub ShapesCut() For Each s In ActiveSheet.Shapes s.Cut Next End Sub -- Don Guillett SalesAid Software "Corey" wrote in message ... I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rather than make Invisible
Actually, too late on Thursday night. <bg
Don Guillett wrote: Friday -- Don Guillett SalesAid Software "Dave Peterson" wrote in message ... Doh. I forgot the left() bits. Option Explicit Sub ShapesCut() dim S as shape For Each s In ActiveSheet.Shapes if lcase(left(s.name,1)) = "s" _ or lcase(left(s.name,1)) = "b" then 'keep it else s.delete 'or .cut?? end if Next s End Sub (Still untested!) Don Guillett wrote: Try this to CUT(delete) all shapes on the active sheet Sub ShapesCut() For Each s In ActiveSheet.Shapes s.Cut Next End Sub -- Don Guillett SalesAid Software "Corey" wrote in message ... I am yet to solve the problem with my Excel file being 56mb is size although ALL photos have been removed but the below may hold the key? Sub DeleteAllPhotos() MsgBox "Are You Sure you want to Delete ALL the Photo's ?", , "...." Worksheets("Photo's").Select Dim lnCnt As Long, sStr As String ' Declare Variables (Dimension Them) Application.ScreenUpdating = False ' Turns off use of the screen, makes exceution faster and less distraction For lnCnt = 1 To ActiveSheet.Shapes.Count ' Common Loop, FOR 1 to 10 = loops 10 times With ActiveSheet.Shapes(lnCnt) ' Everything I now do I do WITH ... until END WITH sStr = Left(.Name, 1) ' assigns the first letter of oject name to sStr If sStr < "S" Then ' if NOT S, as you can see these can be 'nested' If sStr < "B" Then ' if NOT B, as you can see these can be 'nested' '.Visible = False ' make current item invisible '<===== Delete the Photo's instead If sStr = "L" Then .Line.ForeColor.SchemeColor = 8 End If End If End With Next Application.ScreenUpdating = True End Sub How can i adapt this code to Delete rather than make invisible the photos, or is that the same?? Will it also reduce the size of the file? Corey.... -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make VbA invisible | Excel Programming | |||
How Invisible Can You Make Your Code??? | Excel Programming | |||
Make counter invisible | Excel Programming | |||
Make one column Invisible. | Excel Programming | |||
Make "Worksheet Menu Bar" invisible or Delete/hide it's buttons | Excel Programming |