VBA/Excel/Access/Word/Excel/Column
Содержание
- 1 Adjusting Column Widths
- 2 Array to Columns
- 3 Assign the column width of one cell to another cell on the active sheet, using:
- 4 Assign the value to the ColumnWidth property of the ActiveCell using:
- 5 Change column width
- 6 ClearContents method deletes the contents of the range but leaves the formatting intact.
- 7 ClearFormats method deletes the formatting in the range but not the cell contents.
- 8 converts the Integer to a String representation of the same column.
- 9 Is in last column
- 10 Make a range autofit
- 11 Returning the Last Used Cell in a Column or Row with Worksheet
- 12 Select active column
- 13 Select entire column
- 14 Select first column to last column
- 15 The BubbleSort() procedure sorts a column of integer values from lowest to highest value
- 16 The Clear method
- 17 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
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>