The Match Columns macro – Absolutely useless until you need it.

You have two ranges with headers. Some of the headers match, some don’t. In addition, they’re not in the same order.

Select the first range, hold Ctrl before selection the second range and run the below code. All the columns of the second range are now matched to the columns of the first, and all unmatched columns of the second range are now at the end. Tada!

Try it, you might like it,
Thomas

Sub MatchColumns()
‘Matches two ranges by inserting columns in both ranges so each value is on the same column

‘you first need to
‘ select the two data blocks you want to split and match

Dim rg1 As range, rg2 As range
Dim firstMatch As Boolean
Dim i As Long, j As Long, foundCol As Long

application.ScreenUpdating = False

If selection.Areas.Count 2 Then
MsgBox “Select two areas”
Exit Sub
End If

Set rg1 = selection.Areas(1)
Set rg2 = selection.Areas(2)

‘gets the number of unique values in the first rows of range 1 and 2, to be able to run the loop all the way
Dim cUnique As New Collection

On Error Resume Next

With rg1
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

With rg2
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

On Error GoTo 0

‘boolean needed to be able to resize range 2 if required
firstMatch = True

For i = 1 To cUnique.Count
If Len(rg1.Cells(1, i)) = 0 Or rg2.Cells(1, i) = rg1.Cells(1, i) Then
firstMatch = False
GoTo nxt_i:
End If

On Error Resume Next
foundCol = rg2.Rows(1).Find(What:=rg1.Cells(1, i), LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False).Column

If Err 0 Then
Err.Clear
rg2.Offset(, i – 1).Resize(, 1).Insert Shift:=xlToRight
If firstMatch Then Set rg2 = rg2.Offset(, -1).Resize(, rg2.Columns.Count + 1)
Else
rg2.Columns(foundCol – rg2.Column + 1).Cut
rg2.Columns(i + rg2.Column – 1).Insert Shift:=xlToRight
firstMatch = False
End If

nxt_i:

Next

application.ScreenUpdating = True
End Sub

Put borders around separate values using Conditional formatting

Just a quick one to show you that conditional formatting is not only about coloring cells. Assuming you have a table with distinct values in column A, e.g. a customer code, a blend numbers, or anything else (see screenshot 1). You’d like to have borders between each category, just to make it neat. Just select the whole table, add a new rule for conditional formatting (see screenshot 2), use formula =$A2<>$A1 ($ are important), set a top border, validate. You’re done, see screenshot 3.

Screenshot 1, before:

Screenshot 2, Conditional formatting with formula:

Screenshot 3, after:

Try it, you might like it.

Thomas

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)

 

Try it, you might like it.

Thomas

 

Automatic increment

By now, you probably know how to automatically increment series, enter the first two cells of the series, select them, place your pointer on the bottom right corner (until it turns into cross) and drag with a left click.

Now, for dates, you can do the same and more by just typing the first date (remember Ctrl+; will enter today’s date) and dragging with the right click. When you release, you can pick your increment between days, weekdays, month and year. You can also pick Series if you need more flexibility.

Try it, you might like it.

Thomas

Hyperlink from Word / Excel to specific cell in excel file.

Happy Halloween,

It’s Internal Control Procedure time again and you have all your procedures in Word, trying to hyperlink to excel files. All is well, you Insert \ Hyperlink (or right-click \ Hyperlink), select the destination file and you’re done. BUT, it doesn’t really open where you want it. To make sure it opens at the right cell, add the following to your file path: #’SheetName’!CellAddress, as in #Sheet1!A.

You can of course, update the text to display so it doesn’t show the whole path. Check out screenshot below.

Thanks Benjamin for this suggestion,

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

Move bullet items up and down – Outlook, Word, Powerpoint,

OK, it doesn’t have to be all about Excel. If you ever want to reorganize items in a bullet list in MS-Office (Word, Outlook, Powerpoint), use Alt+Shift+Up Arrow (or Down arrow) to move it up, or down.

 

Try it, you might like it.

Thomas