'This will delete the row if the whole row is empty (all columns) Next
9.4 COLUMN EXAMPLES
9.4.1 Making Certain That A Selection Is Only A Single Column Or Row Wide
When you ask the user to provide you with a selection that can only be a single row or column wide, you have no assurance that the user will make a selection that meets your criteria. The following examples show how to check the selection that the user made to insure that it meets these criteria
Example 1 - verifying columns selected
'count the number of columns. If more than one, stop the macro.
If Selection.Columns.Count > 1 Then
MsgBox "Select only a single column. No action taken."
'stop the macros End
End If
'code to execute if selection is a single column Example 2 - verifying rows selected
Dim userRange As Range
'get a range from the user via an input box On Error Resume Next
Set userRange = Application.InputBox( _
prompt:="Select cells on a single row for processing", _ Type:=8, default:=Selection.Address)
On Error GoTo 0
'if no range selected, stop
If userRange Is Nothing Then Exit Sub
'if cells on more than one row selected, display message and stop If userRange.Rows.Count > 1 Then
MsgBox "Select just cells on a single row. " & _ "No action taken"
'stop the macros End
End If
'if selection passes the above text run any code place here
9.4.2 Converting Column Letters To Column Numbers
The following converts columns letters to numbers. For example, supplying "F" to this statement would return the number 5.
Function ColumnNumber(AlphaColumn As String) As Integer ColumnNumber = Columns(AlphaColumn).Column
End Function
9.4.3 Converting Alphabetic Column Labels To Numeric Column Labels
There are several easy ways to convert column labels such as "CF" to a number and use in a Visual Basic statement
AlphaColumn = "CF"
numberColumn = Range(AlphaColumn & "1").Column or
Cells(intRow, Range(AlphaColumn & "1").Column).Value = 5 numberColumn = Columns(AlphaColumn).Column
9.4.4 Getting The Letter Of A Column
The following illustrate several ways to get the column letter of a column:
Sub Approach1
Dim colLetters As String Dim N As Integer
'prompt for a column number, exit if none entered N = Val(InputBox("enter a column number")) If N = 0 Then Exit Sub
With Worksheets(1).Columns(N) 'extract the column letters from the address
'the first worksheet in the workbook is used for convenience colLetters = Left(.Address(False, False), _ InStr(.Address(False, False), ":") - 1)
End With 'display the letter
MsgBox colLetters End Sub
Approach 2:
Public Function ColumnLetter(anyCell As Range) As String ColumnLetter = Left(anyCell.Address(False, False), _ 1 - CInt(anyCell.Column > 26))
End Function Sub Demo()
MsgBox ColumnLetter (ActiveCell) End Sub
9.4.5 Comparing Two Columns
The following code illustrates how to compare column 'A' and 'B' by Visual Basic code. The cells at each column contain figures. If the cell amount in column A is greater than the cell amount in column B, then the cell in column B should have a red color background.
Sample Data:
A1 100 B1 100
A2 150 B2 50 this cell should be 'red' A3 40 B3 20
A4 50 B4 100
The following is the simple solution to this. Please note that it hard codes the ranges.
Sub CompareColumns() Dim CurrCell As Range
'loop through each cell in the selection
For Each CurrCell In Range("B1:B4")
'compare values; .offset(0,1) refers to the cell to the left of CurrCell If CurrCell.Value < CurrCell.Offset(0, -1).Value Then 'color the cell red if true
CurrCell.Interior.ColorIndex = 3 Else
'remove any color if not true
CurrCell.Interior.ColorIndex = xlNone End If
Next End Sub
A more flexible solution is the following, which prompts the user for the first range and then the first cell in the second range.
Sub CompareColumns() Dim cell As Range
Dim firstRange As Range, firstCell As Range Dim I As Integer
'turn on error checking in case cancel selected in inputbox On Error Resume Next
'use Application.InputBox with Type:=8 so that the user must input a range.
'Set the default equal to the current selection
Set firstRange = Application.InputBox( _
prompt:="Please select the first range of cells", _ Type:=8, _
default:=Selection.Address(False, False)) 'exit if cancel selected or no range entered
If firstRange Is Nothing Then Exit Sub
'prompt for the comparison cell related to the first cell of the above range Set firstCell = Application.InputBox( _
prompt:="Please select the cell related to " & _ firstRange.Cells(1).Address(False, False), _
Type:=8)
'exit if cancel selected or no range entered If firstCell Is Nothing Then Exit Sub 'turn off error checking
On Error GoTo 0 I = 0
'rotate through each cell and compare to its related cell For Each cell In firstRange
If firstCell.Offset(I, 0).Value > cell.Value Then firstCell.Offset(I, 0).Interior.ColorIndex = 3
Else
firstCell.Offset(I, 0).Interior.ColorIndex = xlNone End If
'increment I for next cell I = I + 1
Next End Sub
9.4.6 How To Convert Alphabetic Column Labels To Numeric
If you have a column letter and want to turn it into a number, you can use the following approach:
numberColumn = Worksheets(1).Columns(AlphaColumn).Column
9.4.7 How To Copy Multiple Columns At A Time
If you want to copy a group of columns that are together, then you can use a statement like the following:
Columns("A:G").Copy
However, if you want to copy just columns A and columns G, then you must use the following statement instead.
Range("A:A,G:G").Copy
If you used Columns("A:A,G:G").Copy, you would get an error.
9.4.8 How To Delete Columns In Multiple Sheets At One Time
the following deletes D column in all worksheets:
Worksheets(1).Activate Worksheets.Select Columns("D:D").Select Selection.Delete
but the following does not work - only deleting column D from the active sheet:
Worksheets(1).Activate Worksheets.Select
Columns("D:D").Delete
It appears to be one situation that VBA can only handle by selecting,
9.4.9 How To Insert Columns In Multiple Sheets At One Time
the following deletes D column in all worksheets:
Worksheets(1).Activate Worksheets.Select Columns("D:D").Select Selection.Insert
but the following does not work - only deleting column D from the active sheet:
Worksheets(1).Activate Worksheets.Select
Columns("D:D").Insert
It appears to be one situation that VBA can only handle by selecting,
9.4.10 An Insert A Column And Formula Example
In this example, the user needs a macro that inserts column D, puts a formula in D1 =sum(a1:c1) and copies it down as many rows as there is data in column C, The number of rows varies day to day. The following macro accomplishes this task:
Sub FillFormulas() Dim myRng As Range Dim lastRw As Long
'find the last row in column C, starting at cell C1
lastRw = Worksheets("Sheet1").Range("C1").End(xlDown).Row 'write a sum formula to cell D1
Set myRng = Worksheets("Sheet1").Range("D1") myRng.Formula = "=SUM(A1:C1)"
'fill the formula down column D
myRng.AutoFill Destination:=Worksheets("Sheet1") _ .Range("D1:D" & lastRw&)
End Sub
The following is an alternate solution to the above problem:
Sub AlternateFill() Dim c As Range 'insert a column
Columns("D").Insert
'set a range variable equal to the first cell in column C Set c = ActiveSheet.Range("C1")
'loop until a blank is encountered Do While c <> ""
'write a formula using R1C1 notation
c.Offset(0, 1).FormulaR1C1 = "=Sum(RC[-3]:RC[-1])"
'set c to the next cell down Set c = c.Offset(1, 0) Loop
End Sub
9.4.11 Deleting Columns
If you record a macro that deletes a column, you will get code like the following:
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
As long as you know the column letters, you can use the above approach. And, you can simplify it:
Columns("E:G").Delete
However, because Visual Basic does not return column letters, the above approach is very limited when you are writing code. Instead, you must work around this limitation by using the Cells() method and the Range() method. The following examples show how to use these methods to delete columns a number of different ways.
If you wish it to delete the column containing the active cell and the next two columns, you can do it this way:
Dim A As Long, B As Long A = ActiveCell.Column B = A + 2
Range(Cells(1, A), Cells(1, B)).EntireColumn.Delete
The following is another approach that uses range variables to accomplish this task.
Dim startCell As Range, endCell As Range Set startCell = ActiveCell
Set endCell = startCell.Offset(2, 0)
Range(startCell, endCell).EntireColumn.Delete
If the startCell and endCell refer to cells on a sheet that is not the active sheet, then use this approach:
Dim oWS As Worksheet
Set oWS = startCell.Parent
oWS.Range(startCell, endCell).EntireColumn.Delete This could also have been written:
startCell.Parent.Range(startCell, _
endCell).EntireColumn.Delete
The key is qualifying the Range() method with the worksheet when the range being deleted is not on the active sheet.
9.4.12 Setting Column Widths
The following illustrates how to set the width of a column:
Columns("b").ColumnWidth = 25
If you have a range of cells and want to set the column range for this range, do it like the following:
Range("A1:D5").EntireColumn.ColumnWidth = 1 To AutoFit the column widths, use a statement like the following:
Columns("A:A").EntireColumn.AutoFit or
Range("A1:D5").EntireColumn.AutoFit
9.4.13 Setting Column Widths And Row Heights
The following illustrates how to set row widths and column heights:
'this sets the column width and row width of the active cell ActiveCell.EntireColumn.ColumnWidth = 12 ActiveCell.EntireRow.RowHeight = 15
'in this example, variables X, Y, M, and N have been determined by earlier code, and are used to specify a cell range on the active sheet
Range(Cells(X, Y), Cells(M, N)).EntireRow.RowHeight = 15
If the range is on a different sheet, then qualify the range with the sheet name. If the sheet is not in the active workbook, then qualify the sheet with the workbook book:
Workbooks("Some Book").Sheets("some sheet") _ .Cells(4,4).EntireRow.RowHeight = 24
If the range variable "myRange" has been set to refer to a range of cells on a worksheet, then the following would set the column width of the columns in this range to an automatic fit. Because
"myRange" is a range variable, you do not need to qualify it with the worksheet or workbook.
myRange.EntireColumn.ColumWidth = AutoFit
The following example will auto fit columns A through Z of the active sheet:
Columns("A:Z").ColumnWidth = AutoFit
9.4.14 Setting Column Widths To A Minimum Width
The following code will auto-fit all columns, and then check all columns in the used range and if their width is less than 13.5, set the width to 13.5
Sub gbColFit()
Dim sh As Worksheet Dim col As Range
'rotate through all worksheets in the active workbook For Each Sh In ActiveWorkbook.Worksheets 'auto-fit all columns
Sh.Cells.EntireColumn.Autofit
For Each col In sh.UsedRange.Columns
'check the columns in the used range and set to a minimum width If col.ColumnWidth < 13.5 Then
col.ColumnWidth = 13.5 End If
Next col
Next sh End Sub
9.4.15 Setting Column Width And Row Height In Centimeters
The macros below to set the column width and row height in millimeters (print size/100%
zoom):
Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer) 'Set the columnwidth in millimeters (approximately)
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w Columns(ColNo).ColumnWidth = _
Columns(ColNo).ColumnWidth - 0.1 Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w Columns(ColNo).ColumnWidth = _
Columns(ColNo).ColumnWidth + 0.1 Wend
End Sub
Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer) 'Set the rowheight in millimeters
If RowNo < 1 Or RowNo > 65536 Then Exit Sub Rows(RowNo).RowHeight = _
Application.CentimetersToPoints( _ mmHeight / 10)
End Sub
The sample macro below shows how you can change the column width of column C and the row height of row 3 to 3.5 cm:
Sub ChangeWidthAndHeight() SetColumnWidthMM 3, 35 SetRowHeightMM 3, 35 End Sub
9.4.16 Determining The Populated Cells In A Column Of Data
The following illustrates how to find the range of cells in a column that contains data. In this example, the assumption is that the there are no blank cells in the column, and that the data cells contain no blanks:
'this example assumes that the sheet has not been modified by deleting 'rows and that only a uniform block of data is on the sheet:
Set ActiveColArea = Intersect(Columns("a"), _ ActiveSheet.UsedRange)
OR
Set activeColArea = Range(Cells(1,1), Cells(10000,1).End(Xlup)) 'assuming that row 10000 is well below the last entry in the column