It will unhide hidden rows and columns in the selection
All empty cells outside the selection are hidden (see screenshot)
This way the selection is always on the top of your sheet
Sub Mail_Selection() Dim strDate As String Dim Addr As String Dim rng As Range If ActiveWindow.SelectedSheets.Count > 1 Or Selection.Areas.Count > 1 Then Exit Sub Application.ScreenUpdating = False Addr = Selection.Address ActiveSheet.Copy ActiveSheet.Pictures.Delete With Cells .EntireColumn.Hidden = False .EntireRow.Hidden = False End With Range(Addr).Select Set rng = Selection Application.GoTo rng, True With rng.EntireColumn .Hidden = True rng(1).EntireRow.SpecialCells(xlVisible).EntireColumn.Clear rng(1).EntireRow.SpecialCells(xlVisible).EntireColumn.Hidden = True .Hidden = False End With With rng.EntireRow .Hidden = True rng(1).EntireColumn.SpecialCells(xlVisible).EntireRow.Clear rng(1).EntireColumn.SpecialCells(xlVisible).EntireRow.Hidden = True .Hidden = False End With Application.GoTo rng, True rng.Cells(1).Select strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _ & " " & strDate & ".xls" ActiveWorkbook.SendMail "email@example.com", _ "This is the Subject line" ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False Application.ScreenUpdating = True End Sub
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.