How to delete unwanted rows and columns in an Excel worksheet
(It won't take as long as a first look might suggest and it's safe!)
There are two sheets referred to in the procedure below.
Let's call your original sheet YourOriginalSheet
(it represents the actual name of your original sheet).
Let's call the other sheet ShortSheet
, which will contain a copy of only relevant cells.
(1) In YourOriginalSheet
, Select
and Copy
the range of relevant cells (don't paste anywhere yet).
(2) Add a new sheet named ShortSheet
, put the cursor into the cell representing the upper-left-hand corner of the range being copied (probably A1
), and paste like so:
(2a) Paste Special...
> Formulas
[right-click the upper-left-hand corner cell]
(2b) Paste Special...
> Values
(2c) Paste Special...
> Formats
(2d) Paste Special...
> Column Widths
(2e) Include other Paste Special
options that you think might help make the sheet look better.
(3) Press Ctrl-Shift-End
to find the lower-right-hand corner of ShortSheet
to make sure it contains all relevant data.
(4) Save
the workbook. (Save As ...
a new file if you want a backup.)
(5) Delete YourOriginalSheet
(The relevant data is currently saved in ShortSheet
.)
(6) Rename ShortSheet
to the actual name of your original sheet`.
Nothing has changed except to make the workbook a lot smaller, so all macros, external references to this worksheet, etc. should still work.
Here is a macro
to accomplish the steps in (2).
Sub pasteSpecialAll()
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Before using the macro, Select
and Copy
the relevant range, as was done in step (1).
I saved my macro in my Personal.xlsb
workbook for future use everywhere and assigned keypress Ctrl + Shift + V
to it.