Retrieve all comments in a workbook

Hello all,

You want to retrieve all the comments from a workbook in a neat way, without having to print the workbook (you know you can print the comments in a separate page, right?). The following sub will pull all comments and put them in a new worksheet, neatly, with the sheet title, hyperlinked cell reference, the cell value and the cell comment.

Try it, you might like it.

Sub RetrieveCasdfomments()
‘get all comments from a workbook and put them in a new worksheet
Dim rgCmt As Range, rgComments As Range, lRowLoop As Long, shtLoop As Worksheet, shtComments As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets.Add before:=Sheets(1)
Set shtComments = ActiveSheet

With shtComments

‘create and format the comment summary sheet
shtComments.Name = “Comments”

With .Columns(“A:D”)

.VerticalAlignment = xlTop
.WrapText = True

End With

.Columns(“C”).ColumnWidth = 15
.Columns(“D”).ColumnWidth = 60
.PageSetup.PrintGridlines = True

.[a1] = “Sheet”
.[b1] = “Cell”
.[c1] = “Value”
.[d1] = “Comment”

.Rows(1).Font.Bold = True

.Tab.Color = 255
.Tab.TintAndShade = 0

End With

lRowLoop = 2

For Each shtLoop In ActiveWorkbook.Worksheets

‘loop through all worksheets and retrieve the comments
If shtLoop.Name <> shtComments.Name And shtLoop.Comments.Count > 0 Then

On Error Resume Next
Set rgComments = shtLoop.Cells.SpecialCells(xlCellTypeComments)

If Err = 0 Then

For Each rgCmt In rgComments.Cells

If Trim(rgCmt.Comment.Text) <> “” Then

shtComments.Cells(lRowLoop, 1) = shtLoop.Name
shtComments.Hyperlinks.Add Anchor:=shtComments.Cells(lRowLoop, 2), Address:=””, _
SubAddress:=”‘” & shtLoop.Name & “‘!” & rgCmt.Address(0, 0), TextToDisplay:=rgCmt.Address(0, 0)
shtComments.Cells(lRowLoop, 3) = “‘” & rgCmt.Text
shtComments.Cells(lRowLoop, 4) = “‘” & rgCmt.Comment.Text
lRowLoop = lRowLoop + 1

End If

Next rgCmt

Else

Err.Clear

End If

End If

Next shtLoop

shtComments.Activate

‘clean up
If Application.WorksheetFunction.CountA(shtComments.Columns(1)) = 1 Then

MsgBox “No comments in workbook”
Application.DisplayAlerts = False
shtComments.Delete
Application.DisplayAlerts = True

End If

Application.Calculation = xlCalculationAutomatic ‘xl95 uses xlAutomatic
Application.ScreenUpdating = True
End Sub

Thomas

Table Of Contents for multi-tab workbooks

Hi all,

 

A quick and dirty way to create a table of contents for a multi-tab excel workbook. Run the attached macro and it will create a new tab, with the list of all existing tabs and a hyperlink to each of them.

Try it, you might like it.

Sub IndexAllTabs()
‘creates table of contents for workbook
Dim sht As Worksheet, i As Long
Dim shtDone As Worksheet

Set shtDone = ActiveWorkbook.Sheets.Add

On Error Resume Next
shtDone.Name = “TOC”

If Err.Number <> 0 Then
    ActiveWorkbook.Sheets(“TOC”).Delete
    shtDone.Name = “TOC”
    Err.Clear
End If
i = 1

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> shtDone.Name Then
        shtDone.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=””, SubAddress:= _
            “‘” & sht.Name & “‘!A1”, TextToDisplay:=sht.Name
        i = i + 1
    End If
Next

End Sub

 

Thomas

The Great Splitter And Sender Of Reports

On a monthly basis, at least, you’re faced with slew of reports that need to be sent to various managers, with some managers getting one part of the report, some getting others, and some getting all the parts.

You will usually have to copy tabs to new workbooks, save temporary files and e-mail them.

Here comes the Great Splitter And Sender of Reports to make your life easier and generally better: use the Command Center tab to define who gets which tabs in the workbook, set the parameters of the e-mail, etc. then press the Split and Send button to, you guessed it, split and send the tabs. You just have to go to Outlook to press Send on all those e-mails and you’re done.

To include that functionality to one of your report files, copy the Command Center tab and the module mSplitAndSend to your report workbook. Voilà.

Here’s the file: The Great Splitter and Sender of reports.

Try it, you might like it,

Thomas

Explode two lists with all values of the other

From time to time, and I must admit, it doesn’t happen often, but often enough to justify making a macro, you will want to match two lists, e.g. a list of employees and a list of G/L accounts, and get as a result a line for each possible combination of employees and accounts.

Image

Here’s the excel version.

1. Copy the code listed at the end of this post in a standard VB module

2. Select both ranges: you select the employee list with the mouse, then press Ctrl, release the mouse button (while still holding the Ctrl key down) and select the GL list.

3. Run the macro: you now have a new tab with the expanded list ready for consumption.

Try it, you might like it.

Thomas

Here’s the code:

Sub ExplodeLists()
Dim rg1 As range, rg2 As range, shtDest As Worksheet, rgCell As range
Dim lLoop As Long, lRowDest As Long

‘turn off updates to speed up code execution
With application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

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

Set rg1 = selection.Areas(1)
Set rg2 = selection.Areas(2)
Set shtDest = Worksheets.Add

lRowDest = 1

For lLoop = 1 To rg1.Rows.Count
shtDest.Cells(lRowDest, 1).Resize(rg2.Rows.Count, rg1.Columns.Count).Value = rg1.Rows(lLoop).Value
rg2.Copy shtDest.Cells(lRowDest, 1 + rg1.Columns.Count)
lRowDest = lRowDest + rg2.Rows.Count
Next

With application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

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

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

 

The beauty of the R1C1 Reference Style

If you’ve ever recorded a macro in which you manipulate worksheet formulas, you’ve been confronted with the R1C1 reference style, for example as:

Selection.FormulaR1C1=”=SUM(R[-4]C:R[-1]C)”

What’s with the R1C1 thingy? I typed the formula in the worksheet as =SUM(A1:A4), why did Excel change it?

R1C1 is a reference style for excel cells, just like the more ubiquitous A1 reference style. A1 works with a letter code for the column (A) and a number code for the row(1), with absolute positions preceded by the $ sign.  The R1C1 reference works with numbers for both rows and columns, with the prefix R for Rows,  and C for Columns, with the lack of numbers meaning you work in the same row and / or column as the formula. The numbers that follow R and C are either relative positions (between [ ]) or absolute positions (no [ ]).

Examples:

  • R[2]C refers to the cell two rows below the cell in which the formula’s in: R[2] means two rows below, C without any number means same column
  • RC[-1] refers to the cell one column to the left: R without a number means same row as the formula, C[-1] without any number means same column
  • R1C1 refers the cell in the first row and first cell ($A$1): R1 is first row, C1 is first column.

That’s all well and good, but what’s the point, and why would I need to use it?

1. For Macros:

Imagine you want a whole row of totals, summing the four rows above the total row. If you use A1 addressing, your code will be: Range(“A5:E5″).Formula=”=SUM(A1:A4)”. When you run that code, Excel will actually change the formula for you so it stays relative, i.e. in cell E5, the formula in your worksheet will be =SUM(E1:E4). If you were to just apply the formula to cell E5 though, with the code Range(“E5″).Formula=”=SUM(A1:A4)”, then your worksheet formula would be =SUM(A1:A4). Maintaining that type of code is a guaranteed nightmare.

In R1C1 though, both Range(“A5:E5″).FormulaR1C1=”=SUM(R[-4]C:R[-1]C)”  and Range(“E5″).FormulaR1C1=”=SUM(R[-4]C:R[-1]C)” will put the same formula in cell E5, i.e. =SUM(E1:E4), making it a more consistent choice for coding.

2. For Worksheet Formula Maintenance

You’ve just finished writing a formula, copied it to the whole spreadsheet, formatted everything and you realize that you forgot to make a reference absolute: every formula needed to reference Cell B2 but now, they all reference different cells.

Spreadsheet example where only one cell has the correct information

If your formulas are nicely grouped, you can just copy paste the new formulas, but sometimes, those formulas are scattered throughout the worksheet, so how are you going to do a Find/Replace on the cells, considering that one has B5, the other C12, the third D25, etc., etc.? You can replace B5 by $B$2, then C12 by $B$2 but you will be there all night.

The easy way is to update your Reference Style to R1C1.

What does it matter? Well, When you wrote your first formula back in the beginning of this post, B2 was the cell 4 rows above the cell you wrote it in, i.e. R[-4]C. When you copy it across and down, while the A1 reference changes, the R1C1 reference doesn’t. Throughout the whole spreadsheet, it’s R[-4]C.  If you switch to R1C1 Reference Style (see How you can switch section at the end of this post), you can replace R[-4]C by R2C2 ($B$2) with a simple Find / Replace and be done in one fell swoop.

Another example, if you have a spreadsheet with monthly columns, and want to update the formulas that refer to one month’s column to another, you will have to replace B1 by C1, B20 by C20, etc. You could try doing a find/replace to replace B by C, but in the process, you could turn your SUBTOTAL in SUCTOTAL and create a giant mess. If you switch to R1C1 Reference Style, you can replace RC[-4] by RC[-3] with a simple Find / Replace again. Your mileage may vary depending on your particular set of formulas.

3. Review formula consistency

The most accidental shortcut? Probably Ctrl ~, which switches your worksheet from displaying the results of the formulas to displaying the formula itself. By using that shortcut in the R1C1 style, you have a very visual way of checking the consistency of your formulas across a worksheet. Compare the left column, which has the formulas in A1 style to the formulas in the R1C1 style. As the Excel versions progress, there are more and more warnings on formula inconsistency already, but this is one more handy one.

Comparing Formula display in A1 vs. R1C1

 

How can you switch the Reference Style?

Three Options in Excel 2010:

  1. Go to File \ Options \ Formula \ Working with Formulas, Check R1C1 reference style (this is the long way)
  2. Open Visual Basic Editor (Alt + F11), open an Immediate window (Ctrl+G), type application.ReferenceStyle=xlR1C1, Enter. This is the quick, easy fix. To reverse, type application.ReferenceStyle=xla1 and Enter
  3. Put the following macro in your workbook, and add a button for it in your Quick Access toolbar, it’s well worth it.

Sub Shift_R1C1_A1()
If application.ReferenceStyle = xlR1C1 Then
application.ReferenceStyle = xlA1
Else
application.ReferenceStyle = xlR1C1
End If
End Sub

Try it, you might like it.

Thomas

PS: for more on the history of the R1C1 evolution, and after you’ve pressed the Follow button, check out this blog post by excelmate (will open in new tab).