How to modify a procedure to purge unused rows ?

Let's assume that the first spreadsheet that I generate has 5 rows of data.
Then if I manually go to TOOLS, PROTECTION, UNPROTECT SHEET and put in my password
then hightlight rows 6 to 65536 and then hit the DELETE button and save the file, the file size shrinks from 3 MB to 16KB.

Is there a way to do this using VBA Code in the procedure named Private Sub StartDocLNBranch(filename)
that follows ?

Possibly check each row in Column X for a value and on the first row that is empty (for example, row 6)
to the last row, row 65536, delete these rows.

I create over 400 spreadsheeets and send them out as attachments to emails. With each spreadsheet at over 3 MB, my email is getting full.


Private Sub StartDocLNBranch(filename)
 Dim xlApp As Excel.Application
 Dim xlWB As Excel.Workbook
 Dim xlWS As Excel.Worksheet
   
 'open excel template
 Set xlApp = New Excel.Application
 'xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Open(filename)
 Set xlWS = xlWB.Worksheets(1)
 xlWS.Columns.AutoFit
 xlWS.Unprotect PASSWORD:="myPassword"
 xlWS.Range("L2:L65535").NumberFormat = "00000-0000"
 xlWS.Range("L2:L65535").HorizontalAlignment = xlLeft
 xlWS.Range("V2:V65535").NumberFormat = "00000-0000"
 xlWS.Range("V2:V65535").HorizontalAlignment = xlLeft

 xlWS.Cells.Locked = False
 xlApp.Intersect(xlWS.Range("A2:W65535"), xlWS.UsedRange).Locked = True
 xlApp.Intersect(xlWS.Range("Y2:IV65535"), xlWS.UsedRange.EntireRow).Locked = True
 
 With xlApp.Intersect(xlWS.Range("X2:X65535"), xlWS.UsedRange).Validation
    .Delete
    .Add Type:=xlValidateCustom, Formula1:="=OR(UPPER(X2)=""Y"",UPPER(X2)=""N"")"
    .IgnoreBlank = True
    .InCellDropdown = False
 End With

 xlWS.Protect PASSWORD:="myPassword"
 xlWB.Save
 xlWB.Close
End Sub
 
 
 Start Free Trial