Category: Uncategorized

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?

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. 

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.

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:




Sub remove3dBorder()
'
' remove3dBorder Macro
'
'
Dim myT As Table

For Each myT In ActiveDocument.Tables

    myT.Select
    
    'Selection.Rows.HeadingFormat = True
    With Selection.Tables(1)
        With .Borders(wdBorderLeft)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderRight)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderTop)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderBottom)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderHorizontal)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderVertical)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
 
        .TopPadding = CentimetersToPoints(0)
        .BottomPadding = CentimetersToPoints(0)
        .LeftPadding = CentimetersToPoints(0)
        .RightPadding = CentimetersToPoints(0)
        With Dialogs(wdDialogTableTableOptions)
            .AllowSpacing = 0
            .Execute
        End With
        
        .AllowPageBreaks = True
        .AllowAutoFit = True
    End With
    
    ' turn on header row
    myT.Cell(1, 1).Select
    Selection.Rows.HeadingFormat = True
    
    Next myT
End Sub 




[Source Egghead Cafe and Google Answers]