Help to modify macro please

[Brian Tozer]

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?

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


Thanks
Brian Tozer

 

[J.E. McGimpsey]

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.

 

[Brian Tozer]

One way:

Thanks very much J.E.
Off to clear the log-jam.

Brian Tozer