в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:40Z

1 версия

Новая страница

==Adjusting Column Widths==



<source lang="vb">

Sub AdjustColumns()
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Columns.ColumnWidth = 12
Set ws = Nothing
End Sub
</source>

==Array to Columns==



<source lang="vb">

Sub ArrayToColumns()
Dim MyArray()
Dim Cols As Integer
Cols = 5
ReDim MyArray(1 To Cols)
Cells.Clear
i = 1
For c = 1 To Cols
MyArray(c) = i
i = i + 1
Next c
Range(Cells(1, 1), Cells(1, Cols)) = MyArray
End Sub
</source>

==Assign the column width of one cell to another cell on the active sheet, using:==



<source lang="vb">

Sub widthDemo()
Range("C1").ColumnWidth = Range("A1").ColumnWidth
End Sub
</source>

==Assign the value to the ColumnWidth property of the ActiveCell using:==



<source lang="vb">

Sub colDemo()
ActiveCell.ColumnWidth = 20
End Sub
</source>

==Change column width==



<source lang="vb">

Private Sub ChangeColumnWidth(Width As Variant)
If IsNumeric(Width) Then
If Width > 0 And Width < 100 Then
Me.Columns.ColumnWidth = Width
ElseIf Width = 0 Then
Me.Columns.ColumnWidth = Me.StandardWidth
End If
End If
End Sub
</source>

==ClearContents method deletes the contents of the range but leaves the formatting intact.==



<source lang="vb">

Sub content()
Columns("D:D").ClearContents
End Sub
</source>

==ClearFormats method deletes the formatting in the range but not the cell contents.==



<source lang="vb">

Sub format()
Columns("D:D").ClearFormats
End Sub
</source>

==converts the Integer to a String representation of the same column.==



<source lang="vb">

Sub Main()
Debug.Print GetColumnRef(3)
End Sub
Function GetColumnRef(columnIndex As Integer) As String
Dim numAlpha As Integer
Dim firstLetter As String
Dim secondLetter As String
Dim remainder As Integer
numAlpha = columnIndex \ 26
Select Case columnIndex / 26
Case Is <= 1 "Column ref is between A and Z
firstLetter = Chr(columnIndex + 64)
GetColumnRef = firstLetter
Case Else "Column ref has two letters
remainder = columnIndex - 26 * (columnIndex \ 26)
If remainder = 0 Then
firstLetter = Chr(64 + (columnIndex \ 26) - 1)
secondLetter = "Z"
GetColumnRef = firstLetter & secondLetter
Else
firstLetter = Chr(64 + (columnIndex \ 26))
secondLetter = Chr(64 + remainder)
GetColumnRef = firstLetter & secondLetter
End If
End Select
End Function
</source>

==Is in last column==



<source lang="vb">

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function
</source>

==Make a range autofit==



<source lang="vb">

Sub autofit()
range("A1:G1").Columns.autofit
End Sub
</source>

==Returning the Last Used Cell in a Column or Row with Worksheet==



<source lang="vb">

Function GetLastUsedRow(rg As Range) As Long
Dim lMaxRows As Long
lMaxRows = ThisWorkbook.Worksheets(1).Rows.Count
If IsEmpty(rg.Parent.Cells(lMaxRows, rg.Column)) Then
GetLastUsedRow = _
rg.Parent.Cells(lMaxRows, rg.Column).End(xlUp).Row
Else
GetLastUsedRow = rg.Parent.Cells(lMaxRows, rg.Column).Row
End If
End Function
</source>

==Select active column==



<source lang="vb">

Sub SelectActiveColumn()
If IsEmpty(ActiveCell) Then Exit Sub
On Error Resume Next
If IsEmpty(ActiveCell.Offset(-1, 0)) Then Set TopCell = ActiveCell Else Set TopCell = ActiveCell.End(xlUp)
If IsEmpty(ActiveCell.Offset(1, 0)) Then Set BottomCell = ActiveCell Else Set BottomCell = ActiveCell.End(xlDown)
Range(TopCell, BottomCell).Select
End Sub
</source>

==Select entire column==



<source lang="vb">

Sub SelectEntireColumn()
Selection.EntireColumn.Select
End Sub
</source>

==Select first column to last column==



<source lang="vb">

Sub SelectFirstToLastInColumn()
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(16384, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell.Select Else Range(TopCell, BottomCell).Select
End Sub
</source>

==The BubbleSort() procedure sorts a column of integer values from lowest to highest value==



<source lang="vb">

Public Sub BubbleSort()
Dim tempVar As Integer
Dim anotherIteration As Boolean
Dim I As Integer
Do
anotherIteration = False
For I = 1 To 9
If Cells(I, "A").Value > Cells(I + 1, "A").Value Then
tempVar = Cells(I, "A").Value
Cells(I, "A").Value = Cells(I + 1, "A").Value
Cells(I + 1, "A").Value = tempVar
anotherIteration = True
End If
Next I
Loop While anotherIteration = True
End Sub
</source>

==The Clear method==



<source lang="vb">

Sub clear()
Columns("D:D").clear
End Sub
</source>

==The NumberFormat property represents the number format (expressed as a text string) of the Range object: statement changes the number format of column A to percent with two decimal places==



<source lang="vb">

Sub numFormat()
Columns("A:A").NumberFormat = "0.00%"
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Column - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия