Ultimate Google Sheets VLOOKUP Tutorial Series

/in , , , /by

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.

Add a New WorkSheet in Excel – VBA Macro Code

/in , /by

This VBA code will add a new worksheet in your Excel project.

Sub AddNewWorksheet()

 Dim NewWorkSheet As Worksheet
 Set NewWorkSheet = Sheets.Add

End Sub

Convert CrossTab Table to Database List Table – Excel VBA Macro Code

/in , , /by

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.

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.chicagocomputerclasses.com/excel-classes/

Delete All Hidden Columns and Rows – VBA Excel – Code

/in , /by

This VBA code can help you delete all hidden columns and rows from your excel sheet with a click of a button.

Sub hiddendelete()
For lp = 256 To 1 Step -1 'loop through all columns
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1 'loop through all rows
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

WordPress Training – Build While You Learn

/in , /by

Short content

Read more