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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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
news:OsjWz%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:OsjWz%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:OsjWz%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> news:OsjWz%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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
>
>
>

Thanks David, always useful to see how others have tackled issues.

Regards, Rob
"David" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>

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 <[email protected]> wrote in message
news:[email protected]...
> Thanks David, always useful to see how others have tackled issues.
>
> Regards, Rob
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
>
>

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" <$$$$vram26@vsnl@@@@.net&&&> wrote in message
news:[email protected]...
> 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 <[email protected]> wrote in message
> news:[email protected]...
> > Thanks David, always useful to see how others have tackled issues.
> >
> > Regards, Rob
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >>
> > >>
> > >>
> >
> >
>
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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
>>>news:OsjWz%[email protected]...
>>>
>>>>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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>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" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>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
> >>>news:OsjWz%[email protected]...
> >>>
> >>>>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" <[email protected]> wrote in message
> >>>>news:[email protected]...
> >>>>
> >>>>>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
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>
> >>
> >
> >
>

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/
_______