What is the syntax to read rows in to a list box?

<listbox name>.AddItem Sheets("<sheet name>").Cells(<row>, <column num>).Value & ...etc

#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

 

 

What is the syntax to search a range?

Range("<your range here>").Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
'What - data to search for; only mandatory parameter
'value (ex) any VBA data type like 123, 12.3, "name"

'After - a single cell to start searching from
'value (ex) Range("A1"), Range("B5")

'LookIn - to search in formulas, values, or comments
'value (ex) xlValues, xlFormulas, xlComments

'LookAt - look at part of cell or entire cell?
'value (ex) xlWhole, xlPart

'SearchOrder - search by rows or columns
'value (ex) xlByRows, xlByColumns

'SearchDirection - search next cell or previous ones
'value (ex) xlNext, xlPrevious

'the following parameter's values are either true or false
'MatchCase - is search case sensitive?
'MatchByte - only relevant if you have installed double-byte language support
'SearchFormat - search by formatting (which is set using Application.FindFormat)

It is critical to note that the Range.Find method does not return a value, rather, it returns a Range object. If nothing is found, the Range object will be Nothing.

'examples

'returns Emily Carn
Range("B3:B20").Find(What:="Emily").Value

'selects cell B8
Range("B3:B20").Find(What:="Emily", MatchCase:=False).Select

'returns 19
Range("B3:B20").Find(What:="Emily", After:=Range("B17")).Row

What is the syntax to insert a column?

Range(“<column reference>”).EntireColumn.Insert

‘example
Range(“C:D”).EntireColumn.Insert ‘inserts 2 columns from column C

What is the syntax to find the last column of data?

<integer variable> = Sheets("<worksheet’s name>").Cells(1, Sheets("<worksheet’s name>").Columns.Count).End(xlToLeft).Column

What is the syntax to hide/unhide columns?

Columns(“<column letter>”).EntireColumn.Hidden = <true or false>

‘example
Columns(“D:E”).EntireColumn.Hidden = true

What is the syntax to change column width?

Columns(“<column letter>”).ColumnWidth = <width in pixels>

Bear in mind that standard column width in Excel is 15.

‘examples
Columns(“B:D”).ColumnWidth = 32

 

What is the syntax to change column width?

Columns(“<columns>”).ColumnWidth= <width in pixels>

‘examples
Columns(“D:G”).ColumnWidth = 4
Worksheets(“Sheet3”).Columns(“A”).ColumnWidth=233

What is the syntax to change the font size of a column?

Columns(<column number>).Font.Size=<Font pt size>

 

What is the syntax to clear a range?

'To clear all contents, including styling
Range(“<your range>”).Clear

'To clear only text
Range(“<your range>”).ClearContents

'examples
Range(“D:D”).Clear ‘clears column D
Range(“A2:C5”).Clear ‘clears range A2:C5
Range(“B1:B3”, “D1:E5”).Clear ‘clears multiple ranges
Sheets("Input").Range("pasteRange").ClearContents 'clears text only

 

What is the syntax to delete a column?

Columns("<Column Letter>").Delete

(ex) Columns("A").Delete
(ex) Columns("H").Delete