If you are a Microsoft Office User. . .

If you are a Microsoft Office user you might find email subscriptions to these sites of value.

Microsoft Excel Tips:

I often wish I could do something a little odd in Excel like deleting old data from a worksheet based on particular date. Here's the tip on how to do it:

This can be rather easily done with a macro. All you need to do is have the macro step through the data and compare the date in each row to today's date. If the date is less than today, then the Delete method is used on the EntireRow object.

Sub DeleteRows1()
    Dim x As Long
    Dim iCol As Integer

    iCol = 7 'Filter all on Col G

    For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
        If Cells(x, iCol).Value < Date Then
            Cells(x, iCol).EntireRow.Delete
        End If
    Next
End Sub

In this example, the macro checks column G (in the iCol variable) for the date. If your date is in a different column, then you should make the change to the variable. Depending on the number of rows of data in your worksheet, the macro may also take quite a while to run.

If you notice a lag in performance, then you may want to use a different approach. The following example uses the AutoFilter capabilities of Excel to first filter the data to show only the old data, and then deletes those rows.

Sub DeleteRows2()
    Dim Dates As Range
    Dim nRows As Double
    Dim currDate As Variant

    'Format dates as text
    Range("Dates").NumberFormat = "@"
    'Today's date in number format
    currDate = CDbl(Date)
    Range("Dates").AutoFilter Field:=1, _
      Criteria1:="<" & currDate
    nRows = Range("Dates").Rows.Count
    Rows("2:" & nRows).Select
    Selection.Delete Shift:=xlUp
    Range("Dates").AutoFilter
    Range("Dates").NumberFormat = "m/d/yyyy"
    Range("C2").Select
End Sub

This macro presumes that you have taken the step of assigning a name to your data range. Select all the cells in your data table—including any heading row—and give it the name "Dates." When you run the macro, it uses this range as the target for the AutoFilter.

The above tip is from a free email subscription at exclribbon.tips.net.

Microsoft Word Tips:

Or in Microsoft Word, you might find that when you begin typing a document, the text starts out in Normal style especially when typing short bits of text with no punctuation at the end. Then when you press Enter a couple of times to move to a new line, the style changes to Heading 1 with different font and color (of course). Upon backspacing the text reverts to the default font and style.

Believe it or not, this is a built-in feature of Word. In its never-ending quest to be as helpful as possible, Word tries to anticipate how you want your text formatted. If you type in a paragraph that contains a small number of words (typically five words or less) and you don't put a punctuation mark at the end of those words, then the program—as soon as you press Enter—assumes you are entering a new heading and applies a heading style to what you entered.

Here's how you can turn off that feature of Word:

1. Display the Word Options dialog box. (In Word 2007 click the Office button and then click Word Options. In Word 2010 and Word 2013 click the File tab of the ribbon and then click Options.)


2. Make sure that Proofing is selected at the left side of the dialog box.


3. Click the AutoCorrect Options button. Word displays the AutoCorrect dialog box.


4. Make sure the AutoFormat As You Type tab is displayed. (See Figure 1.)

Figure 1. The AutoFormat As You Type tab of the AutoCorrect dialog box.

1. Clear the Built-in Heading Styles check box.


2. Click on OK.

That tip if from a free email subscription at wordribbon.tip.net.