FREE - Learn to write macros from scratch, make buttons and simple procedures to automate tasks. Play with Loops and Excel Triggers. Teach Excel to make decisions with Conditional Logic and much more!
Description | Code | Example | Use Case | |||
---|---|---|---|---|---|---|
General | General | Gets the last row on the ActiveSheet (whatever sheet is currently active) in column A (1st column) | Function getLastRow()    'gets last row on current sheet    getLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row End Function | 'get the last row for the current sheet into variable wsLR wsLR = getLastRow() | You have to find the boundaries of a table or range. The best way to do that is to find where the last row is and then form the boundaries from there. Example: Your table is columns A, B and C. It grows as more entries are added. Your macro needs to run a Vlookup on that table using the correct amount of fields. So, you get the last row in order to get the accurate table length. searchTerm = "Dan" foundJobTitle = Application.WorksheetFunction.Vlookup(searchTerm, ActiveSheet.Range("A2:C" & getLastRow() ), 2, 0) | |
General | General | Gets the last row on the ActiveSheet (whatever sheet is currently active) in column A (1st column) | Function getLastRowCustSht(shtName) 'gets last row in col A on a custom sheet 'put the name of the sheet in the shtName parameter getLastRowCustSht = Sheets(shtName).Cells(Rows.Count, 1).End(xlUp).Row End Function | 'get the last row in col A for sheet named "Sheet3" into variable wsLR wsLR = getLastRowCustomSht("sheet3") 'get the last row in col A for sheet named "Employees" into variable empLR empLR = getLastRowCustomSht("Employees") | ||
Files, Folders | VBA Functions | Create a Folder and all subfolders at once with this custom VBA function. If one of the folders already exists, it will move forward without trying to create it. | Public Sub makeFolder(filePath As String) Dim arrFolders, i As Integer, strPath As String, ws as Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") 'segment all folders and subfolders arrFolders = Split(filePath, "") 'loop through all folders. For i = 0 To UBound(arrFolders) - 1 Â Â Â Â Â strPath = strPath & arrFolders(i) & ""
Next i     'If folder doesn't exist, make it.     If Not FolderExists(strPath) Then MkDir strPath End Sub | 'make a folder and ALL needed subfolders from a variable Dim curPath as String curPath = "C:makethisfolderpath" makeFolder curPath 'make a folder from a cell value (cell c5 on sheet2) makeFolder Sheet2.Range("c5").Value | ||
General, Files, Folders | General, VBA Functions | dynamic test | Public Sub makeFolder(filePath As String) Dim arrFolders, i As Integer, strPath As String, ws as Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") 'segment all folders and subfolders arrFolders = Split(filePath, "") 'loop through all folders. For i = 0 To UBound(arrFolders) - 1 Â Â Â strPath = strPath & arrFolders(i) & "" 'If folder doesn't exist, make it. If Not FolderExists(strPath) Then MkDir strPath Next i End Sub | 'make a folder and ALL needed subfolders from a variable Dim curPath as String curPath = "C:makethisfilepath" makeFolder curPath 'make a folder from a cell value (cell c5 on sheet2) makeFolder Sheet2.Range("c5").Value | test |