Excel Tips & Tricks 7 :

• Dealing with Dates like:  20090623
• Printing gridlines on a worksheet
• Trim All extra spaces
• Proper Case
• Lower Case
• Upper Case
• Delete blank columns
• Gantt Chart

Dealing with Dates
Suppose you are looking at a worksheet which contains dates which can't be formatted as "real" dates because of their structure:

Select the date(s), and use Data tab, Text-to-Columns, [Excel2003:Data|Text to Columns]

Which brings up the Text-to-Columns wizard:

Even though the dates are fixed width, in this case you can simply click "Next" twice.

In step 3 of wizard Select Date, YMD:

If you click Finish now, the result will replace the dates. You can select another destination cell:

Here's the result:

1. On the Page Layout tab, in the Sheet Options group, select the Print check box under Gridlines.

   

    Note   If the check boxes in the Sheet Options group appear dimmed, it may be because you have a chart, image, or other object selected on the worksheet. If you cancel that selection, you'll be able to use the check boxes.

2. Click Microsoft Office Button , and then click Print.

   Keyboard shortcut  You can also press CTRL+P.

3. In the Print dialog box, click OK.

Trim All extra spaces

Sub TrimALL()
Do Until ActiveCell = ""
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.value = Application.Trim(cell.value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Proper Case

Sub Proper_Case()
'above name of the macro

'the next line selects the column(s)
Range(Selection, Selection.End(xlDown)).Select
'The next line setup the active cell in the selected area as an individual item.
Set C = ActiveCell

' X in the for each loop is really the C or the active cell
' so the macro will follow the selected range to the end
' Loop to cycle through each cell in the specified range.
For Each x In Selection
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.value = Application.Proper(x.value)


Next
End Sub

Lower Case

Sub Lower_Case()
'above name of the macro

'the next line selects the column(s)
Range(Selection, Selection.End(xlDown)).Select
'The next line setup the active cell in the selected area as an individual item.
Set C = ActiveCell

' X in the for each loop is really the C or the active cell
' so the macro will follow the selected range to the end
' Loop to cycle through each cell in the specified range.
For Each x In Selection
'LCase is the function to make all letters lower case.
x.value = LCase(x.value)


Next
End Sub

Upper Case

Sub Upper_Case()
'above name of the macro

'the next line selects the column(s)
Range(Selection, Selection.End(xlDown)).Select
'The next line setup the active cell in the selected area as an individual item.
Set C = ActiveCell

' X in the for each loop is really the C or the active cell
' so the macro will follow the selected range to the end
' Loop to cycle through each cell in the specified range.
For Each x In Selection
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.value = UCase(x.value) ' Proper


Next
End Sub

Delete blank columns

Sub DeleteTheBlankColumns()
'Jesse built 8-18-08
'finds all blank columns and then deletes them

LastColumn = ActiveSheet.UsedRange.Columns.Count

Application.ScreenUpdating = False

For R = LastColumn To 1 Step -1

If Application.CountA(Columns(R)) = 0 Then Columns(R).Delete

Next R

End Sub

Gantt Chart

Sub gantt_chart()
Dim rge As Variant
Dim mn As Variant
Dim shtname As Variant
'defines the variables
rge = Selection.Address()
'get the cell address
mn = Selection.Offset(1, 1)
'return the min value for the scale
Title = InputBox("Please enter the title")
'Asks the user for title
shtname = ActiveSheet.Name
'retains the name of current sheet
Application.ScreenUpdating = False
'Turns screen updating off
Charts.Add
'Create a paper model chart
ActiveChart.ChartWizard Source:=Sheets(shtname).Range(rge), _
Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels _
:=1, SeriesLabels:=1, HasLegend:=1, Title:=Title, _
CategoryTitle:="", ValueTitle:="", _
ExtraTitle:=""
' Basic chart definition
ActiveChart.Legend.Delete
'deletes the legend
ActiveChart.SeriesCollection(1).Select
'activates series 1
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
'definition for the border for series 1
Selection.InvertIfNegative = False
'turns Invert if negative to false
Selection.Interior.ColorIndex = xlNone
'indicates that the area is set to none
ActiveChart.PlotArea.Select
'select the chart plot area
ActiveChart.Axes(xlCategory).Select
'select axis(1)
With ActiveChart.Axes(xlCategory)
.ReversePlotOrder = True
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
End With
'axis 1 definition
ActiveChart.Axes(xlValue).Select
'select axis(2)
With ActiveChart.Axes(xlValue)
.MinimumScale = mn
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
' Axis(2) definition
End Sub

' EXAMPLE of what the data should look like.

'              
'         
'    
'  
'    
'    Follow directions below carefully!!
'   
'     


' 2. Select cell A2 and format it with the month/day/year date format you want to use on the chart.
' 3. Highlight A2:D8. To run the macro, choose Macro from the Tools menu, and select the gantt_chart macro. In version 97, click Macro on the Tools menu, then click Macro and select the gantt_chart macro.
' 4. Enter the chart title when prompted.
' The Gantt chart should now be displayed on a new chart sheet. Additional formatting can be added as needed.

Webmaster: E-Mail Address
Copyright © 1997 [BCA]. All rights reserved. -
Revised: February 13, 2006.