Reducir tamaño de Archivos de Excel

Introduce el siguiente Codigo en un nuevo modulo del editor de Visual Basic de excel

Sub Limpiar_rangos()

Dim Hoja As Worksheet

Dim rF As Range, rC As Range

Dim Dir$, fFin&, cFin&, TI&, TF&

With ActiveWorkbook

TI = FileLen(.FullName)

MsgBox "Tamaño inicial: " & VBA.Format(TI, "#,##0") & " bytes."

For Each Hoja In .Worksheets

With Hoja

Set rF = .UsedRange.Find(what:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Set rC = .UsedRange.Find(what:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

If rF Is Nothing Then

fFin = 2

Else

fFin = rF.Row + 1

End If

If rC Is Nothing Then

cFin = 2

Else

cFin = rC.Column + 1

End If

If .ProtectContents Then

If MsgBox("La hoja " & .Name & " se encuentra protegida." & vbLf & vbLf & _

"¿Desea desprotegerla antes de continuar?", vbYesNo, "¡Hoja protegida!") = vbYes Then Desproteger Hoja

Else

.Range(.Cells(fFin, 1), .Cells(.Rows.Count, 1)).EntireRow.Clear

.Range(.Cells(1, cFin), .Cells(1, .Columns.Count)).EntireColumn.Clear

End If

End With

Set rF = Nothing

Set rC = Nothing

Next Hoja

.Save

TF = FileLen(.FullName)

MsgBox "Tamaño final: " & VBA.Format(TF, "#,##0") & " bytes." & vbLf & vbLf & _

"El archivo se redujo en: " & VBA.Format(TI - TF, "#,##0") & " bytes." & _

" (" & FormatPercent(Abs(TI / TF - 1), 2) & ")"

End With

End Sub

Sub Desproteger(ByRef Hoja As Worksheet)

Dim Proteger As Object

Set Proteger = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=893, Recursive:=True)

With Hoja

.Visible = True

.Activate

If InStr(Proteger.Caption, "p") Then

Proteger.Execute

End If

If .ProtectContents = True Then MsgBox "La clave no es correcta.", vbCritical

End With

End Sub