One way:

I presume this is just to preserve formatting, right (since there
would be no values to save if myLastRow was < 50)?

Public 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
If myLastRow < 55 Then
.Range(.Cells(56, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
If myLastRow < 50 Then _
.Range(.Cells(myLastRow + 1, 1), _
.Cells(49, 1)).EntireRow.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
End If
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub

Note that if the last row is, say 35, that the formatting of rows
50:55 will be in 36:41 after the macro runs.


In article ,
"Brian Tozer" wrote:

I wish to use the following well publicized macro to remove blank rows in a
worksheet.
However I wish to tell it to ignore rows 50 to 55 inclusive.
How would I modify it to achieve this?