Hi all,
I am writing a script that will:
- loop through all the sheets in the workbook
- delete all charts
- select all rows that have data (there will be some empty rows interspersed but this is ok)
- within this selection delete all empty rows
This is what I have assembled so far:
Many thanks, I'm learning from you guys by leaps and bounds!
Alex
I am writing a script that will:
- loop through all the sheets in the workbook
- delete all charts
- select all rows that have data (there will be some empty rows interspersed but this is ok)
- within this selection delete all empty rows
This is what I have assembled so far:
Code:
'Delete all chartObjects in ALL worksheets AND delete empty rows
Sub DeleteChartsAllSheets()
Dim Ws As Worksheet, chtObj As ChartObject, i As Long
With Application 'turn off calculation and screen updating to speed up the macro.
.Calculation = xlCalculationManual
.ScreenUpdating = False
For Each Ws In ThisWorkbook.Worksheets 'Cycle through Sheets
For Each chtObj In Ws.ChartObjects 'Delete objects of type Chart
chtObj.Delete
Next
--------------------------------------------------------
HERE I'M HAVING PROBLEMS:
'Manually select rows from which I want to delete empty rows;
NEED TO DO THIS AUTOMATICALLY
--------------------------------------------------------
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next
Next Ws
.Calculation = xlCalculationAutomatic 'Turn screen updating and calculation back on
.ScreenUpdating = True
End With
End Sub
Many thanks, I'm learning from you guys by leaps and bounds!
Alex