Excel: Removing Blank Rows

Excel has a powerful filtering capability that lets you view, among other options, the data without blank rows. But how do you remove the blank rows from your data? The following VBA code does the following:

1. Select a cell in the maximum row
2. Check if the selection is blank
3. Delete if blank
4. Move up 1 cell

      Dim x As Integer
      ' Set numrows = number of rows of data.
      NumRows = 1400 -2
      ' Select max cell
      Range("B1400").Select
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.
         If Selection.Text = "" Then
            Selection.EntireRow.Delete
         End If
         ' Selects cell down 1 row from active cell.
         ActiveCell.Offset(-1, 0).Select

There is probably a smarter way to select the maximum number of rows, but for my data it was fixed.

Bathroom Documentation Confusion

I went out to lunch the other day and the restaurant had a confusin sign on the door to the bathrooms:

Sometimes there is just nothing to say. I mean, what is this?

Am I only allowed in the bathroom if I am a peeping Tom? Are the women here so beautiful that I would climb over a wall to see them? Or are they so ugly that I would climb over a wall to get away?

Check that Font

There is a website that lets you see your own custom text in every font that is on your computer. Just go to this website:

http://www.wordmark.it/

[Source Cybertext]

AIT: Book Properties in Word

I can never find the link in the AIT knowledge base to the list of the book properties along with the corresponding Word document properties. Here they are:

• the Title property stores the information in the Title field
• the Super title property stores the information in the Subject field
• the Version property stores the information in the Keywords field, and
• the By line property stores the information in the Author field. 

[source AIT KC]

AIT: Removing a Topic from a Version of a Book with Variants

Author-it 5.5 provides a greatly enhanced variant capability. It adds a hierarchy to the variants, which allow it to manage different versions of a book. As an example, we use the variant "designRev" with the following values:

A0

--> A1

    --> A2

This hierarchy allows us to create a book with versions A0, A1, or A2. But, what happens when I add a new topic to version A2? If I put it in the book TOC, then it will show up in versions A1 and A0 as well. 

To solve this problem, follow these steps:

1. Locate the topic in the library.
2. Right click on it.
3. Choose Variant --> Convert to Variant
4. Select the checkbox next to designRev and then click the checkbox for A2 beneath it.

Now, open the book and select A2 from the variant filter. The topic is shown in the TOC. If you select A0 or A1 from the variant filter, the topic is still in the TOC, but it has a strike-through and will not appear in the output.

Word: Collapse All in the Navigation Pane

I like the Navigation Pane in Word 2010, but there is one problem: Word defaults to showing all of the headings expanded (meaning that it shows all heading levels), and if you have a lot of headings it makes it hard to find the right one. Shouldn't there be an easy way to collapse all the headings? And then also an easy way to expand them all?

Well, there is. Just right click on any topic and then choose Expand All or Collapse All.

Word: Dropped Capital

To create a big letter at the beginning of a paragraph (called a dropped capital), try this:

1. Click anywhere in the paragraph
2. Click the Insert tab on the Ribbon
3. Click the Drop Cap icon in the Text group

[Source Yahoo Answers]

HowTo: Conditional Formatting Emails in Outlook

If you gets lots of email, then this tip is for you. 

It would be nice to have emails that are color coded for specific categories. For example, emails that are only to you are one color and emails from some group distribution list are another color. Do accomplish this task, one can set up conditional formatting.

First, click on: View -> View Settings -> Conditional Formatting

Second, Add a new rule, which should be easy to figure out.

For all the details and screen shots, refer to this link.

[Source here]

Macro: Remove Spacing Between Table Cells

I received a document that had hundreds of tables, all with a nice 3D border. Then somebody said that the border looked ugly so I started writing a macro to change all the borders to something more plain. Changing the borders was simple, but the cells had space between them which resulted in a double border. Getting rid of this space was not so simple.

Finally, I found this little macro at the Egghead Cafe:

With Dialogs(wdDialogTableTableOptions)
   .AllowSpacing = 0
   .Execute
End With

A second method (that I did not try) is to use this:

Selection.Tables(1).Spacing = -1

The full macro follows:

PPT: Applying the Footer to All Slides

Sometimes you need to reapply the footer to all of your slides to get back missing page numbers and other footer-type information. To do this task:

1. Click on Insert -> Slide Number
2. Check the Footer box
3. Click Apply  to All

[Source Microsoft]

Word: Random Lorem Ipsum Text

Did you ever need to insert some random text into a document? Here are two magic ways to create random text in Word 2007 & 2010:

Random Text: 

Type "=rand(x,y)" in your document and then hit Enter.

Lorem Ipsum:

Type "=lorem(x,y)" in your document and then hit Enter.

x is the number of paragraphs.

y is the number of sentences per paragraph.

[Source GroovyPost or MS Support]

Macro: Repetitive Replacing of Table Cells

Here is today's time saver:

I had some tables (actually 1000 pages of tables!) where I needed to replace cells containing various text (denoted by comments from the SME) with a standard word (which was Reserved in this case). Normally, I would triple-click in the cell and then control-v to paste in the text (5 clicks/keys), but there were too many places that I needed to make this change.

Using the following macro this task was reduced to two clicks, once in the cell and then once on the button for the macro. There was one other requirement for this macro, which was that if the cell was in bold, it received slightly different text.

Sub makeCellReserved()
   Selection.SelectCell

   If Selection.Range.Bold = True Then
       Selection.Text = "RESERVED"
   Else
       Selection.Text = "Reserved"
   End If
End Sub

AIT: Highlighting Variants

This post describes how I use highlighting and release states in Author-It to make reviewing documents easier. This procedure assumes that you have a main book along with another book that uses variants of the objects in the main book. The variants are either variant topics or embedded variant topics. 

By using this procedure, the reviewer can review one complete document, and then just review the highlighted sections in the second document.

Setup

1. Open AIT Administrator
2. Select Security -> Release States
3. Add a new state:

a. Right click -> Add State
b. State name: HighlightVar
c. Right click over the new state, select Check Row
d. Click the box near the end of the row for HighlightVar / Review Color
e. Select yellow
f. Check that the boxes are checked for all relevant users

4. Do step 3 again to add the state DoNotHighlightVar, but skip steps d and e.

5. Close AIT Administrator
6. Open AIT
7. In your library, sort the objects by variant
8. Select (using Ctrl+click) all variants of the default object
9. Right mouse click, then select Change Release State -> HighlightVar

The variant of the main document can now be published with highlighted sections.

Split Excel Columns into Multiple Rows

I am always forgetting how to do this, so today I am writing it down. When you have one long column of data in Excel and you want to split it into multiple rows of data (such as rows with 5 columns), how do you do it?

Assuming the data is in column A, then put into B1:

=Indirect(Address((Row(A1)-1)*5+Column(A1),1))

Copy to the right four times and then down as far as you need to. If you want to change the number of columns in the result, then change the '5' to the # of columns.

[Source OzGrid]

Remove All BookMarks

Sometimes documents can get loaded with useless bookmarks. Here is a macro to remove them:

Sub RemoveAllBookmarks()
    Dim objBookmark As Bookmark

    For Each objBookmark In ActiveDocument.Bookmarks
        objBookmark.Delete
    Next
End Sub

Fontastic

Remember, it's not how you feel, but how you look.



Font selection chart:

Author-It: Pasting Text with Formatting

For those of you using Author-It, there is an unfortunate feature that text that is pasted into a topic (from a Word doc), gets a "No Paragraph Style". You then have to select the text and apply an AIT style. At the latest Author-It User Group meeting, the way to get around this was revealed.

Follow these steps:

1. From the main window select: Import -> (Transformation) Profiles
2. Select Word Documents
3. Click Properties
4. Check the box next to Use for Paste

Now, when you want to paste text into AIT, click:
Paste -> Paste Using Import Profile -> Word Documents

That's it. Happy pasting.