To remove unused part & reset used range, What is key role of XlWhole & XlPart in find method?

[Chirag R Raval]

Dear All,

Various types of macros on the web found that use various approach to remove unused part from the sheet.

Finally that code seems reliable , which only trap cells contain literal text or numbers, & not Formatting or formula .

I found very helpful forever 2 Macros from the web that remove extra rows & columns beyond actual used range and reset the last cell ,both macros, used range find method.

Macro 1
Without Xlpart Or Xlwhole

Sub DeleteUnusedFormats()
        Dim lLastRow As Long, lLastColumn As Long
        Dim lRealLastRow As Long, lRealLastColumn As Long
        With Range("A1").SpecialCells(xlCellTypeLastCell)
            lLastRow = .Row
            lLastColumn = .Column
        End With
        lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row
        lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, _
                  xlByColumns, xlPrevious).Column
        If lRealLastRow < lLastRow Then
            Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
        End If
        If lRealLastColumn < lLastColumn Then
            Range(Cells(1, lRealLastColumn + 1), _
                  Cells(1, lLastColumn)).EntireColumn.Delete
        End If
        ActiveSheet.UsedRange    'Resets LastCell
    End Sub

Above don't use Xlpart or XlWhole in find method...

Macro 2
with use of XlWhole only in find method..

'Note: This code may not work correctly if the worksheet contains merged cells. To check 'your worksheet, you can run the TestForMergedCells code.
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

'================================
Sub TestForMergedCells()

  Dim AnyMerged As Variant

  AnyMerged = ActiveSheet.UsedRange.MergeCells

  If AnyMerged = False Then
      MsgBox "no merged"
  ElseIf AnyMerged = True Then
      MsgBox "all merged"
  ElseIf IsNull(AnyMerged) Then
      MsgBox "mixture"
  Else
      MsgBox "never gets here--only 3 options"
  End If

End Sub

(1) it is effective to not to use Xlpart or XlWhole in Macro1?
(2) what is key roll of XlWhole & XlPart in find method?
(3) what is the reason Why not to use XLPart in Macro2?

Though , Just 1 minus point of above 2 codes that just simple single literal space remains in cell, that counted as used cell. If, this limitation can be remove from this codes, then can be most reliable to reset used range.

can any one guide about it? it will be appreciated forever...

Regards,

Chirag Raval

 

[Chihiro]

It's enumeration for xlLookAt.

See link for bit more detail.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xllookat-enumeration-excel