Databases Reference
In-Depth Information
Columns are a special type of entity in Excel because often a column will only hold data of a
specific type. When numerous rows of data are present in a Worksheet, it is good to have all the
columns sized large enough such that none of the data in them is truncated (if practical). The next
subroutine will autosize a Worksheet such that all columns will be able to hold the largest row of
data without truncating it.
Sub AutoSizeSheet(wkbook, wksht)
'Select Workbook and Worksheet
ActivateWorkbook (wkbook)
Worksheets(wksht).Select
'Select All Cells
Cells.Select
'Autosize Columns
Cells.EntireColumn.AutoFit
'Unselect Cells
Range("A1").Select
End Sub
Sometimes, a column will contain formulas, and the user will wish that the column contained
static values that will not change. The following subroutine will convert any column to hold
values only. The value that the cell will hold will be the value the formula generated when the
subroutine is invoked. This subroutine is especially useful when copying a Worksheet to create
a report.
Sub ConvColToValues(wkbook, wksht, Col)
'Convert a Given Column (with formulas) to Values only
Workbooks(wkbook).Worksheets(wksht).Columns(Col).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub
The text data type is unique in that it retains data in exactly the same manner as it is extended.
It does not try to convert dates or times (or what the application “thinks” are dates and times)
to certain formats. Text-type columns are excellent because anything added to them is never
converted to anything different. The following subroutine will convert an existing column to type
text.
Sub SetColTypeAsText(ByVal wkbook, ByVal wksht, ByVal Col)
'Sets a column format to a specific type
Workbooks(wkbook).Worksheets(wksht).Columns(Col).Select
Selection.NumberFormat = "@"
End Sub
This subroutine can be taken a step further by converting an entire sheet to type text as follows.
Sub SetWkshtTypeAsText(ByVal wkbook, ByVal wksht)
'Sets a column format to a specific type
Workbooks(wkbook).Worksheets(wksht).Cells.Select
'Cells.Select
Selection.NumberFormat = "@"
End Sub
