One of the more common tasks done with VBA is copying filtered rows into a new sheet.
I noticed that most of the Excel users I encounter do this by recording a macro that captures the following movements:
- Click the top-left corner of the table
- Press Ctrl+Shift+Down Arrow
- Press Ctrl+Shift+Right Arrow
- Press Ctrl+C to Copy
- Start a new Workbook and then Paste
This usually leaves them with a macro that looks something like code seen below.
Note the Selection.End(xlDown) and Selection.End(xlToRight). This is really the hard way to go; plus this could possibly be dangerous if the data in the table is not reliably filled in completely.
A much easier and safer way is to use the built-in AutoFilter.Range object of the worksheet.
This code does the same action more cleanly.
Now you know.
Like this tip? You’ll find lots more of these gems in this awesome book.
Yes…there’s a redneck on the front cover, but don’t let that deter you. The author is extraordinarily smart and handsome.
Click the book see.