The ultimate series covering everything you need to know about Google Sheets VLOOKUP function. Most of it applies to Microsoft Excel as well. Make sure you watch the whole series if you want in-depth understanding of VLOOKUP function and everything related. Other functions included: INDEX, MATCH, IFERROR, UNIQUE, QUERY & more.
This VBA code will add a new worksheet in your Excel project.
|
1 2 3 4 5 6 |
Sub AddNewWorksheet() Dim NewWorkSheet As Worksheet Set NewWorkSheet = Sheets.Add End Sub |
Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/
If you work with databases and regular data tables you probably had a situation when someone gave you a regular Excel 2 dimensional spreadsheet that is not that useful if you need to use the data within a database and run some queries or just make PivotTables in Excel. The Macro code below will transform your CrossTab table to a regular database list table.
This script will create a new sheet and prompt you to enter a starting point for your converted data. Then it will render the data in 3 columns as a regular database data table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub CrossTabToDatabase() Dim DataTable As Range, OutputRange As Range Dim RowOutput As Long Dim r As Long, c As Long Dim WS As Worksheet On Error Resume Next Set DataTable = ActiveCell.CurrentRegion If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then MsgBox "Select a cell within the summary table", vbCritical Exit Sub End If DataTable.Select Set WS = Sheets.Add Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8) ' Convert the range RowOutput = 2 Application.ScreenUpdating = False OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3") For r = 2 To DataTable.Rows.Count For c = 2 To DataTable.Columns.Count OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1) OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c) OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c) OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat RowOutput = RowOutput + 1 Next c Next r End Sub |
Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/
This VBA code can help you delete all hidden columns and rows from your excel sheet with a click of a button.
|
1 2 3 4 5 6 7 8 |
<em>Sub hiddendelete()</em> <em>For lp = 256 To 1 Step -1 'loop through all columns</em> <em>If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else</em> <em>Next</em> <em>For lp = 65536 To 1 Step -1 'loop through all rows</em> <em>If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else</em> <em>Next</em> <em>End Sub</em> |
Average Salaries – City of Chicago Fire Police Department EarningsMarch 21, 2017 - 1:56 amAverage Salaries – City of Chicago Fire Department EarningsMarch 14, 2017 - 4:51 am
Ultimate Google Sheets VLOOKUP Tutorial SeriesMarch 14, 2017 - 4:44 amGoogle Sheets SPLIT Function Tutorial – Similar to Text to Columns in ExcelMarch 5, 2017 - 11:46 am
How to Pull Data From Other SpreadSheets using Google Sheets IMPORTRANGE FunctionMarch 5, 2017 - 11:42 am
Locations Serving: Chicago, Loop, Downtown, North, North-East, North-West and West Suburbs, including Evanston, Northbrook, Schaumburg, Hoffman Estates and Naperville IL.
