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




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext