Skip to content

applescript – How do I find Excel sheet with wrong used range?

  • by
Spread the love

How do I convert this VBA code to AppleScript? The code finds used last row and column from used range and compares it with the last row and column using Find. I would like a text file to be created with a list of worksheets that have issues.

Sub DefinedPrintArea2() 'with.find
    Dim sht As Worksheet
    Dim lastrow As Long, lastcol As Long
    Dim myfile As String
    Dim textfile As Integer
    myfile = SelectFolder & "test file3.txt"         'select the folder where you want tosave the text folder
    textfile = FreeFile
    Open myfile For Output As textfile
    For Each sht In ActiveWorkbook.Worksheets
        With Sheets(sht.Name)
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
        lastcol = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious, _
            lastrow = 1
            lastcol = 1
        End If
        End With
        lastrow2 = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        lastcol2 = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Column
        If lastrow > 40 Or lastrow2 > 40 Then
        lastrow = 40
        lastrow2 = 40
        End If
        If lastrow <> lastrow2 Or lastcol <> lastcol2 Then
            Print #textfile, "there's a problem with the sheet " & sht.Name & " , please check"
            Print #textfile, "last row using used range : " & lastrow2 & ", last column using used range : " & lastcol2
            Print #textfile,
        End If
        ActiveSheet.Range(Cells(1, 1), Cells(lastrow, lastcol)).Select
        sht.PageSetup.PrintArea = Selection.Address
        Close #textfile
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *