VBA Code
No Row or Column Objects
There are no Row or Column objects and there are no Rows or Columns collections.
It is possible however to use the properties from the activesheet object
ActiveSheet.Columns(icolumn)
This returns a Range object that refers to a particular column.
Range("C1").ColumnWidth = Range("A1").ColumnWidth
icount = Selection.Columns.Count - returns the number of columns currently selected
ActiveSheet.Rows
ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Rows(10).Cells(6).Select
The EntireColumn and EntireRow properties return the columns or rows in which the given range is located.
These are then treated as normal ranges of cells.
ActiveSheet.Outlines.ShowLevels RowLevels:=2
Range("B2:B10").EntireColumn.Interior.ColorIndex = 27
Application.Intersect(Activesheet.Rows(2).Cells, Activesheet.Columns(4).Cells)
Columns
ActiveSheet.Columns
ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Columns("A:C").Delete
Range("A1:E10").Columns("C").Insert
Number of rows currently selected
lcount = Selection.Rows.Count
Number of columns in a multiple selection
Dim iareacount As Integer
Dim itotal as Integer
itotal = 0
For iareacount = 1 to Selection.Areas.Count
itotal = itotal + Selection.Areas(iareacount).Columns.Count
Next iareacount
Call MsgBox(itotal)
ActiveSheet.UsedRange.Row
Also called GetColumnName, ColumnNumberToLetter
ColumnNumberToNa,e
Question
I would like to hide a row if certain values are entered in three cells. For e.g. if United Kingdom is selected in Cell C3 and C5 and CI is selected from cell C10, I would then have Row 16 hidden. I would like this to be dynamically i.e. updated whenever the value in the cell changes.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Me.Range("C3,C5,C10")
If Not Intersect(rng, Target) Is Nothing Then
Rows(16).EntireRow.Hidden = Range("C3").Value = _
"United Kingdom" And Range("C5").Value = _
"United Kingdom" And Range("C10") = "C1"
End If
End Sub
Formatting
Columns("A:B").HorizontalAlignment = Constants.xlLeft
Range("A4").EntireRow.Interior.Color = 5296274
Column Numbers to Letters
This is extremely useful if you need to switch between the Range object and the Cells object.
Remember that using column letters in your code makes it significant easier to understand and quicker to debug.
Public Function Col_Letter(ByVal iColNo As Integer) As String
Dim sstartletter As String
On Error GoTo AnError
Col_Letter = Left(Cells(1, iColNo).Address(False, False), _
Len(Cells(1, iColNo).Address(False, False)) - 1)
Exit Function
AnError:
Call MsgBox(Err.Number & " - " & Err.Description)
End Function
Public Function Col_Letter2(ByVal iColNo As Integer) As String
Dim sstartletter As String
On Error GoTo AnError
Select Case iColNo
Case 0: Col_Letter2 = Chr(90)
Case Is <= 26: Col_Letter2 = Chr(iColNo + 64)
Case Else
If iColNo Mod 26 = 0 Then
Col_Letter2 = Chr(64 + (iColNo / 26) - 1) & Col_Letter(iColNo Mod 26)
Exit Function
End If
sstartletter = Chr(Int(iColNo / 26) + 64)
Col_Letter2 = sstartletter & Col_Letter2(iColNo Mod 26)
End Select
Exit Function
AnError:
Call MsgBox(Err.Number & " - " & Err.Description)
End Function
Converting a Column Letter to its equivalent Column Number
Public Function Col_Number(ByVal sColChar As String, _
Optional ByVal sWshName As String = "") _
As Integer
Dim istartnumber As Integer
On Error GoTo AnError
If Len(sColChar) = 1 Then
If sWshName <> "" Then _
Col_Number = Worksheets(sWshName).Range(sColChar & "1").Column
If sWshName = "" Then Col_Number = Range(sColChar & "1").Column
Else
istartnumber = Range((Left(sColChar, 1)) & "1").Column
Col_Number = ((istartnumber)) * 26 * (Len(sColChar) - 1) + _
Col_Number(Right(sColChar, Len(sColChar) - 1))
End If
Exit Function
AnError:
Call MsgBox(Err.Number & " - " & Err.Description)
End Function
ColumnWidth Property
The ColumnWidth property returns or sets the width of the columns in the specified range,
The return value is in units which represents the number of characters which can be displayed in the column.
Each unit represents the width of one character used in the Normal style.
For variable or proportional size fonts (eg Arial, Verdana) the width of the zero character "0" is used.
Columns("A:D").ColumnWidth = 20
Width Property
The Width property can be used to return the column width in points.
The relationship between Width and ColumnWidth is a little odd.
Changing the Width
Selection.ColumnWidth = 10
Range("C5").ColumnWidth = 14
Dim icolno As Integer
For icolno = 1 to 10
Debug.Print icolno & " - " & Cells(1,icolno).ColumnWidth
Next icolno
Obtaining the Width
If all the columns in the range do not all have the same width then Null is returned.
Dim iColumnWidth As Integer
iColumnWidth = Columns("A").ColumnWidth 'this is in units
Dim iWidth As Integer
iWidth = Columns("A").Width 'this is in points
AutoFit Columns
The AutoFit method changes the width of the column(s) to obtain the best fit for the columns.
This changes the ColumnWidth accordingly.
Columns("A:H").EntireColumn.AutoFit
Range("F4").CurrentRegion.EntireColumn.AutoFit
1 point = 1/72 inches
1 point = 35/100 millimeters
Blank new worksheet
Default - 8.43 (64 pixels_
?? - 8.50 (56 pixels)
Row Heights
Selection.RowHeight = 10
Rows("2:4").RowHeight = 16
Sub DisplayRowHeightsColumnWidths()
Dim lrowno As Long
Dim iColNo As Integer
Dim scolchar As String
For lrowno = 4 To 16
Debug.Print lrowno & " - " & Rows(lrowno & ":" & lrowno).RowHeight
Next lrowno
For iColNo = 4 To 26
scolchar = Col_Letter(iColNo)
Debug.Print scolchar & " - " & Columns(scolchar & ":" & scolchar).ColumnWidth
Next iColNo
End Sub
Selecting
Columns("A").Select
Columns("A:D").Select
Columns(2).Select
Rows(4).Select
Rows("1:4").Select
ActiveCell.EntireRow.Select
ActiveCell.EntireColumn.Select
Selection.EntireRow.Select
Selection.EntireColumn.Select
Inserting Rows
Selection.EntireRow.Insert
Inserting Columns
Sheet1.Range("A:A").EntireColumn.insert
Sheet1.Range("A:A").Insert
Deleting Rows
Rows(4).Delete
Rows(4:4).Delete
Rows(4:4).Delete Shift:=xlUp
Rows(4:8).Delete Shift:=xlUp
Cells.EntireRow.Delete
Selection.EntireRow.Delete
Deleting Columns
Cells.EntireColumn.Delete
Columns("A:D").Delete Shift:=xlLeft
Deleting Empty Rows
This uses the COUNTA worksheet function to determine if a row is empty.
Sub DeleteEmptyRows()
Dim lLastRow As Long
Dim lRowNo As Long
lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For lRowNo = lLastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(lRowNo)) = 0 Then
Rows(lRowNo).Delete
End If
Next r
End Sub
Deleting Blank Rows in a Selected Range.
Type:=8 argument specifies a Range object; input value must be a range.
Sub DeleteBlankRows()
Dim rng As Range
Dim selectedRng As Range
Dim iRowCount As Integer
Dim iForCount As Integer
Set selectedRng = Application.Selection
Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)
iRowCount = selectedRng.Rows.Count
For iForCount = iRowCount To 1 Step -1
If Application.WorksheetFunction.CountA(selectedRng.Rows(iForCount)) = 0 Then
'Delete entire row.
selectedRng.Rows(iForCount).EntireRow.Delete
'Delete partial row.
'selectedRnd.Rows(iForCount).Delete
End If
Next
End Sub
Deleting All Visible Rows
This deletes all the visible rows between 1 and 100.
ActiveSheet.Range(Cells(1, 3), Cells(100, 3)).SpecialCells(xlCellType.xlCellTypeVisible).EntireRow.Delete
Hiding Rows
Cells.EntireRow.Hidden = False
Hiding Columns
Cells.EntireColumn.Hidden = False
Columns("B:D").EntireColumn.Hidden = False
VBA - Freeze Panes
ActiveWindow.FreezePanes = False
Split Panes
Returns or sets the row number where the window is split into panes (the number of rows above the split) Read/write Long.
This example splits the active window so that there are 5 rows above the split line.
ActiveWindow.SplitRow = 5
Returns or sets the column number where the window is split into panes (the number of columns to the left of the split line). Read/write Long.
This example splits the window and leaves 3.5 columns to the left of the split line.
ActiveWindow.SplitColumn = 3.5
Returns or sets the location of the horizontal window split, in points. Read/write Double.
This example sets the horizontal split for the active window to 216 points (3 inches).
ActiveWindow.SplitHorizontal = 216
Returns or sets the location of the vertical window split, in points Read/write Double.
This example sets the vertical split for the active window to 216 points (3 inches).
ActiveWindow.SplitVertical = 216
Grouping
Level 1 is for rows that are not grouped
Rows("1:1").OutlineLevel = 1
Level 2 is for rows that have been grouped
Rows("1:1").OutlineLevel = 2
Scenario
If you add an Outline to rows 5:10 and then add another Outline to rows 5:10
Rows("5:5").OutlineLevel = 3
for the same rows that have been grouped again
Loop all rows and find the maximum outline level
Loop again all the maximum levels remove one level
If different rows are grouped then they are grouped on the same level
Rows that are grouped need a row below them to contan the Group Title
If there is no blank row then outlines are joined together.
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev