Right-click in drag

You obviously know that you copy / paste a cell by clicking on its lower right corner, and dragging it down or across while holding the left mouse button down.
For some cell values, e.g. dates or numbers, you can even switch between an incremented copy and a straight copy by holding or not the Ctrl key. By default, dates will increment and numbers won’t, and holding Ctrl will switch the behavior.
NOW, if you perform this dragging with the right-click instead of the left-click, you’re getting a lot more options (see screenshot below), like filling in weekdays, or months. This is quite the time saver.

Options stemming from a right-click drag

Try it, you might like it.

Thomas

PS: Try selecting multiple cells before dragging and see how that affects the results you’re getting.

Special Cells – They’re special (at least that’s what Barney said)!

Special cells will make your interaction with Excel much more enjoyable, and they can be used both in worksheets and VBA, making them a wise investment.
1. From the worksheet:

The F5 key will take you to the Go To Box. There, the Special button awaits you. Once you press it, you’re just one step away from the magic:

Choose any of the options, press OK and two things can happen. If you’ve selected a range, the Special Cells will be selected within that range. If you’ve just selected one cell, the Special Cells on the whole worksheet will be selected.

The possibilities are endless. One example: if you have a table with row headers that you’d like to copy down into the empty rows below:

  • Select the range
  • F5 \ Special \ Blanks \ OKĀ  (or the shortcut version F5 \ Alt+S \ k \ Enter
  • =B2 (or the cell right above your first selected blank
  • Ctrl + Enter
  • All the cells are now updated.
  • You can now select the whole column and do a copy \ paste values to get rid of the formulas.

 

There are of course many more uses for Special Cells, but this will get you going.

 

2. in VBA

You can access the Special Cells through the SpecialCells member of the Range Class, as in:

Range(“A1:L200″).SpecialCells(xlCellTypeBlanks).value=”Not Blank Anymore”

to fill in the blank cells of a range, or, much more useful, to copy only the visible cells of a range that’s been filtered (by the way, if you can avoid loops and use autofilter instead, your macros will thank you):

Sheets(“Sheet1”).Range(“A1:L200”).SpecialCells(xlCellTypeVisible).copy Sheets(“Sheet2”).cells(1,1)

 

You can play around with the Macro Recorder to get the different options, but here’s the list from the MSDN site:

  • XlCellType constants
  • xlCellTypeAllFormatConditions. Cells of any format
  • xlCellTypeAllValidation. Cells having validation criteria
  • xlCellTypeBlanks. Empty cells
  • xlCellTypeComments. Cells containing notes
  • xlCellTypeConstants. Cells containing constants
  • xlCellTypeFormulas. Cells containing formulas
  • xlCellTypeLastCell. The last cell in the used range
  • xlCellTypeSameFormatConditions. Cells having the same format
  • xlCellTypeSameValidation. Cells having the same validation criteria
  • xlCellTypeVisible. All visible cells
  • XlSpecialCellsValue constants:

– xlErrors
– xlLogical
– xlNumbers
– xlTextValues

 

Try it, you might like it.

Thomas

 

Copy format of cell to whole column – shortcut

How do you copy the format of a cell to its whole column. Easy with the mouse, faster with the keyboard.

Alt H F P (to copy format)

Ctrl+Space (to select the whole column).

You’re already done. Also works with Shift+Space to copy the format to the whole row.

Try it, you might like it.

Thomas