A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0-9 !@$* active | api | Application | array | chart | clipboard | color | date and time | debug | Error | file | filter | find | format | log | loop | named range | PPT | Pivot Table | record | udf | workbook | wrapper Continue at Database_code 948 Steps to 6 Figures by Learning Excel-VBA and Other Skills |
| • | Insert a column - Columns(lCol).EntireColumn.Insert ' recorded | |
| • | Learn via Macro recorder | |
| • | Select a column - Columns(i).select | |
| • | Activecell.formulaR1C1 = .... - code from macro recorder | |
| • | Selection.Copy - copies the active selection to the clipboard | |
| • | Selection.Insert Shift:=xlToRight - recorded | |
| • | Selection.copy | |
| • | Columns("A:A").ColumnWidth = 7.43 - recorded | |
| • | Sheets("name").select - selects a sheet | |
| • | Range(selection, selection.End(xlDown)).Select - select area - recorded | |
| • | Selection.autofilter = turns off filter | |
| • | range(...).Autofilter Field:=1, criteria1:="=" & sValue, Operator:=xlAnd = recorded sets a filter | |
| • | Selection.Delete Shift:=xlUp ' recorded | |
| • | Selection.cut - Clipboard | |
| • | Selection.NumberFormat = "m/d/yyyy" | |
| • | VLookup with RC[-44] from recorded code | |
| • | Selection.Merge | |
| • | Formatcols - | |
| • | Borders - Record the code | |
| • | Selection.EntireColumn.Hidden = True ' recorded | |
| • | Selection.PasteSpecial ... ' recorded | |
| • | Selection.End(xlUp).select ' recorded | |
| • | Application.Calculation = xlCalculationManual ' recorded | |
| • | selection.PasteSpecial Paste:=xlPasteColumnWidths, ..' recorded | |
| • | Rows("1:1").RowHeight = 52.5 ' recorded | |
| • | ActiveWorkbook.Worksheets("....").Autofilter.sort.sortfileds.clear ' clears a filters sorting ' recorded | |
| • | Applicaton.WindowState = xlMaxmized ' recorded | |
| • | Learn by using the Macro recorder | |
| • | FormatXyz - udf - to format data usually via recorded code, | |
| • | Macro recorder hardcode removal | |
| • | Selection.CurrentRegion.Select | |
| • | Application.CalculateFull ' recorded | |
| • | RunQuery( ) - udf db - runs a query that returns 1 value and not a recordset | |
| • | Selection.Autofill destination:=Range("H2:I" & r) | |
| • | Selection.Pastespecial Paste:=xlPasteValues... 'recorded | |
| • | Selection.Replace what:="#N/A", Replacement:="0", LookAt... 'Recorded | |
| • | Sheets(2).visible = True | |
| • | ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ' recorded | |
| • | Protecting a Sheet | |
| • | Activesheet.PivotTables("PivotTable11").PivotFields("CtryOfDom").PivotItems ' recorded | |
| • | ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ... ' recorded | |
| • | ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh ' recorded | |
| • | ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFields 'recorded | |
| • | Selection.WrapText = False ' recorded | |
| • | ActiveWindow.SelectedSheets.Delete - recorded to delete multiple sheets (displayalerts=false) to turn off warning | |
| • | Selection.NumberFormat = "mmm-yy" | |
| • | Selection.Numberformat = "#,##0.0,,," | |
| • | Selection.Font.Underline = xlUnderlinestyleSingle | |
| • | ActiveSheet.PivotTables("pt1").PivotFields("Acct_nbr").Autosort xldescending, sort ' recorded | |
| • | ActiveSheet.PivotTables("pt1").PivotFields("coper_id").PivotFilters.Add Type:=xlTopCount, DataFiled:=... ' recorded | |
| • | ActiveSheet.PivotTables("pt1").ColumnGrand = True ' recorded | |
| • | Application.ErrorCheckingOptions.BackgroundChecking = False ' recorded optimize | |
| • | Application.Autocorrect ' record | |
| • | DeleteExternalNames - deletes named ranges starting with External_ ' recorded | |
| • | GetRecodset (sSQL, Optional maxRecords) as ADODB.Recordset ' | |
| • | Dim rs as New ADODB.Recordset | |
| • | WebQuery - internet - udf - run a url and loads the output to a worksheet - recorded | |
| • | Seletction.Style = "Percent" ' recorded | |
| • | s,Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ::-""??_);_(@_)" 'recorded (g) | |
| • | formatting routines - almost all are recorded and modified | |
| • | Application.WindowState = xlMaximized - recorded | |
| • | Application.WindowState = xlNormal - recorded | |
| • | A01 Dim rsCOB As DAO.Recordset | |
| • | OpenRecordset | |
| • | As DAO.Recordset | |
| • | Selection.OnAction = "TOC1.AddTocSheet" ' Select the shape first via ActiveSheet.Shapes.Range(Array("Button 1")).Select | |
| • | Selection.Characters.Text = "Add TOC" ' after selecting a button, you can change the text | |
| • | Finding the Macro being Recorded | |
| • | What you can record and what you can't (loops, decisions, flexibility) | |
| • | Recording a Macro and Generalizing it |