Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Hi,
The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Hi Rob,
Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message ... Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Thanks Peter, need to experiment with where I place this but get the general
idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message ... Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message ... Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
If you delete the column or the row, then the formatting for that column
and/or row is also deleted. In fact, the formatting may be what is causing you to need to run this routine in the first place. so you may be chasing your tail if you delete the rows and or columns and then replace the formatting that was deleted. -- Regards, Tom Ogilvy "Rob" wrote in message ... Thanks Peter, need to experiment with where I place this but get the general idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message ... Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message ... Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
need to experiment with where I place this
As Tom says you may be "chasing your tail" on this, but you could fit into your original code with something like this: Dim oCht As ChartObject, cnt As Long For Each wks In ActiveWorkbook.Worksheets cnt = wks.ChartObjects.Count If cnt Then ReDim ChPlacement(1 To cnt) 'array to store Placement cnt = 0 For Each oCht In wks.ChartObjects cnt = cnt + 1 ChPlacement(cnt) = oCht.Placement oCht.Placement = xlFreeFloating Next End If 'original code If cnt Then cnt = 0 For Each oCht In wks.ChartObjects cnt = 0 + 1 oCht.Placement = ChPlacement(cnt) 're-apply orignal Placement Next End If Next 'wks Regards, Peter T "Rob" wrote in message ... Thanks Peter, need to experiment with where I place this but get the general idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message ... Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message ... Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Tom,
The formatting covers a range something like A1:F130 whereas if I press End and Home keys the cursor moves to cell X7368. The file originates from an external source and seems to generate a vast range that increases the file size 10 fold. If it were a single sheet file I'd do manually but the file is often 10 or more sheets with various data range on each. Thanks, Rob "Tom Ogilvy" wrote in message ... If you delete the column or the row, then the formatting for that column and/or row is also deleted. In fact, the formatting may be what is causing you to need to run this routine in the first place. so you may be chasing your tail if you delete the rows and or columns and then replace the formatting that was deleted. -- Regards, Tom Ogilvy "Rob" wrote in message ... Thanks Peter, need to experiment with where I place this but get the general idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message ... Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message ... Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Hi Rob,
I was trying to accomplish something similar to what you are trying to do, only I wanted to delete all of the source data. I could not find the post or that helped me out directly, but the following code creates a picture of your chart. It will not change the appearance of the chart. It worked great for me. Hope it will help you. Dim chartShp As Shape Dim exLeft As Single, exTop As Single Set chartShp = ActiveSheet.Shapes(ChartObjectName) exLeft = chartShp.Left exTop = chartShp.Top chartShp.CopyPicture xlScreen chartShp.Delete ActiveSheet.Paste Selection.Left = exLeft Selection.Top = exTop "Rob" wrote: Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Thanks David, always useful to see how others have tackled issues.
Regards, Rob "David" wrote in message ... Hi Rob, I was trying to accomplish something similar to what you are trying to do, only I wanted to delete all of the source data. I could not find the post or that helped me out directly, but the following code creates a picture of your chart. It will not change the appearance of the chart. It worked great for me. Hope it will help you. Dim chartShp As Shape Dim exLeft As Single, exTop As Single Set chartShp = ActiveSheet.Shapes(ChartObjectName) exLeft = chartShp.Left exTop = chartShp.Top chartShp.CopyPicture xlScreen chartShp.Delete ActiveSheet.Paste Selection.Left = exLeft Selection.Top = exTop "Rob" wrote: Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
there is another method without using vba
click the chart you will get in the formula bar =sereis(.........(in cell addresses...) highlight this formula including the sign <= hit function key F9 the formula bar changes to (=seriess.......actual dta..........) hit <enter now you can remove the data from the sheet. chart is based on actual data and not cell values. try that. Rob wrote in message ... Thanks David, always useful to see how others have tackled issues. Regards, Rob "David" wrote in message ... Hi Rob, I was trying to accomplish something similar to what you are trying to do, only I wanted to delete all of the source data. I could not find the post or that helped me out directly, but the following code creates a picture of your chart. It will not change the appearance of the chart. It worked great for me. Hope it will help you. Dim chartShp As Shape Dim exLeft As Single, exTop As Single Set chartShp = ActiveSheet.Shapes(ChartObjectName) exLeft = chartShp.Left exTop = chartShp.Top chartShp.CopyPicture xlScreen chartShp.Delete ActiveSheet.Paste Selection.Left = exLeft Selection.Top = exTop "Rob" wrote: Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
I wasn't aware in my earlier posts the OP was trying to delete cells with
chart source data. If he is that's a neat suggestion. Only thing to be aware of is if the formula string approaches 1024 characters (eg many points with big decimals + long XValues). What I do is convert to named arrays, the only limit being 5461 points per series in xl97 & xl2000 (afaik). But quite a lot of code. Regards, Peter T "R.VENKATARAMAN" &&& wrote in message ... there is another method without using vba click the chart you will get in the formula bar =sereis(.........(in cell addresses...) highlight this formula including the sign <= hit function key F9 the formula bar changes to (=seriess.......actual dta..........) hit <enter now you can remove the data from the sheet. chart is based on actual data and not cell values. try that. Rob wrote in message ... Thanks David, always useful to see how others have tackled issues. Regards, Rob "David" wrote in message ... Hi Rob, I was trying to accomplish something similar to what you are trying to do, only I wanted to delete all of the source data. I could not find the post or that helped me out directly, but the following code creates a picture of your chart. It will not change the appearance of the chart. It worked great for me. Hope it will help you. Dim chartShp As Shape Dim exLeft As Single, exTop As Single Set chartShp = ActiveSheet.Shapes(ChartObjectName) exLeft = chartShp.Left exTop = chartShp.Top chartShp.CopyPicture xlScreen chartShp.Delete ActiveSheet.Paste Selection.Left = exLeft Selection.Top = exTop "Rob" wrote: Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Rob -
If it's the formatting that is bloating the file, you don't want to retain it. If the cells contain unseen characters, you can clear the cell contents without affecting the formats (or deleting the cells) if you use .ClearContents instead of ..Delete in your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Rob wrote: Tom, The formatting covers a range something like A1:F130 whereas if I press End and Home keys the cursor moves to cell X7368. The file originates from an external source and seems to generate a vast range that increases the file size 10 fold. If it were a single sheet file I'd do manually but the file is often 10 or more sheets with various data range on each. Thanks, Rob "Tom Ogilvy" wrote in message ... If you delete the column or the row, then the formatting for that column and/or row is also deleted. In fact, the formatting may be what is causing you to need to run this routine in the first place. so you may be chasing your tail if you delete the rows and or columns and then replace the formatting that was deleted. -- Regards, Tom Ogilvy "Rob" wrote in message ... Thanks Peter, need to experiment with where I place this but get the general idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message . .. Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message .. . Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Just for clarification, clearcontents won't delete formats, but doing that
won't reduce the size of your file based on what you (the OP) have stated so far. I think your whole statement about maintaining formats was probably a mistatement anyway, but only you know what you meant. -- Regards, Tom Ogilvy "Jon Peltier" wrote in message ... Rob - If it's the formatting that is bloating the file, you don't want to retain it. If the cells contain unseen characters, you can clear the cell contents without affecting the formats (or deleting the cells) if you use .ClearContents instead of .Delete in your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Rob wrote: Tom, The formatting covers a range something like A1:F130 whereas if I press End and Home keys the cursor moves to cell X7368. The file originates from an external source and seems to generate a vast range that increases the file size 10 fold. If it were a single sheet file I'd do manually but the file is often 10 or more sheets with various data range on each. Thanks, Rob "Tom Ogilvy" wrote in message ... If you delete the column or the row, then the formatting for that column and/or row is also deleted. In fact, the formatting may be what is causing you to need to run this routine in the first place. so you may be chasing your tail if you delete the rows and or columns and then replace the formatting that was deleted. -- Regards, Tom Ogilvy "Rob" wrote in message ... Thanks Peter, need to experiment with where I place this but get the general idea of what it does. Need to figure out similar for formatting. Thanks again, Robert "Peter T" <peter_t@discussions wrote in message . .. Hi Rob, Before you delete your rows/cols run this. Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects oCht.Placement = xlFreeFloating Next You might want to temporarily store original "Placement" settings and re-apply when done. Keep in mind when you delete rows/cols any objects therein are not deleted, just a little lost! Regards, Peter T "Rob" wrote in message .. . Hi, The following code removes unwanted rows and columns, albeit it's also removing formatting and charts that I want to keep in the worksheet. Is there a way to retain the formatting and charts but also delete unwanted rows and columns that are making the file much too large? Thanks, Rob 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells but not charts
Tom Ogilvy wrote: Just for clarification, clearcontents won't delete formats, but doing that won't reduce the size of your file based on what you (the OP) have stated so far. I think your whole statement about maintaining formats was probably a mistatement anyway, but only you know what you meant. I agree with Tom's last statement. I wasn't sure why the formatting would need to be saved. I used ClearContents to remove data, but not formats. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
Delete ALL Charts in a WorkSheet (VB) | Charts and Charting in Excel | |||
ActiveWorkbook.Charts.Delete | Excel Programming | |||
Macro to delete charts | Excel Programming |