Skip to content

applescript – How to find excel sheet with wrong used range?

Spread the love

How to convert this vba code to AppleScript? The code find used last row and column from used range and compare it with last row and column using find. would like a text file to be created with list of worksheet that has issue.

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 *