.
#1:
Paste this macro into a COPY ONLY OF THE WORKBOOK and run it.
Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet
For Each ws In Worksheets
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
'Clear everything below column A last cell and row 1 last cell
ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws
End Sub
It looks at all the empty cells/row below the last row of visible data, on each sheet, and clears out any extraneous data that is present but it not visible.
If it works on the COPY OF THE WORKBOOK, you can simply change the name of the copy to the original name and proceed.
#2:
If the above doesn't work ... click on a cell in sheet 1 / col A. Then simultaneously press CTRL / DOWN ARROW. This will take you to the bottom of the sheet.
Highlight the bottom 5 or 10 rows by clicking on the row numbers on the left side.
Now without clicking on any cells, scroll back up to the first blank row below your visible data. Hold down the SHIFT key and click on that first empty row.
Now, right click the highlighted rows, select DELETE.
These instructions in #2 do the same thing as #1 instructions, except you are manually performing the actions ... AND ... sometimes the macro in #1 doesn't
work for all workbooks.
Bookmarks