Classes are held by a professional with real-life day to day advanced Excel experience. We try to concentrate our focus on training students to be ready for working environment.
At your office, home, conference room.. you name it. Training at your location, at your convenience.
Sessions can & will be catered to your particular needs.
Classes are held by a professional with real-life day to day advanced Excel experience. We try to concentrate our focus on training students to be ready for working environment.
At your office, home, conference room.. you name it. Training at your location, at your convenience.
Sessions can & will be catered to your particular needs.
This macro is useful in case you have a data table in the following format and you want to transform it to a single cell separated by commas.
43680 |
43681 |
43659 |
43677 |
43578 |
43522 |
43680;43681;43659;43677;43578;43522 |
Option 1:
Sub ConcatColumnValues() Range("B1") = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ";") End Sub
Option 2:
Public Sub TextEr() rs = Rows.Count lr = Cells(rs, 1).End(xlUp).Row stxt = "" For Each oj In Range("A1:A" & lr) If stxt = "" Then stxt = oj Else stxt = stxt & ";" & oj End If Next oj Range("b1").NumberFormat = "@" Range("b1") = stxt End Sub
This formula is useful in case you have a data table in the following format and you want to transform it to “Preferred Results” format.
A-Data-1 | B-Data-1 | C-Data-1 | D-Data-1 |
A-Data-2 | B-Data-2 | C-Data-2 | D-Data-2 |
A-Data-3 | B-Data-3 | C-Data-3 | D-Data-3 |
A-Data-4 | B-Data-4 | C-Data-4 | D-Data-4 |
A-Data-1 |
A-Data-2 |
A-Data-3 |
A-Data-4 |
B-Data-1 |
B-Data-2 |
B-Data-3 |
B-Data-4 |
C-Data-1 |
C-Data-2 |
C-Data-3 |
C-Data-4 |
D-Data-1 |
D-Data-2 |
D-Data-3 |
D-Data-4 |
$A$1:$D$4 – change to your data range
A$7:A7 – change to the first cell where your results data begins. In our example that would be the cell holding A-Data-1 data.
Apply the formula to the first data results cell and fill down.
=INDEX($A$1:$D$4,MOD(ROWS(A$7:A7)+ROWS($A$1:$D$4)-1,ROWS($A$1:$D$4))+1,INT((ROW()-ROW($A$7))/ROWS($A$1:$D$4))+1)
This VBA code will add a new worksheet in your Excel project.
Sub AddNewWorksheet() Dim NewWorkSheet As Worksheet Set NewWorkSheet = Sheets.Add End Sub
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
We can automate very complex tasks for Excel. Those repetitive tasks that you had to do before you now don’t have to. We are here to make your life easy and get things done fast.
Things you were spending weeks to get done will now be complete within minutes. Think of the savings you were missing all these years!
Not only we automate tasks in Excel, we connect it with other data sources, APIs and World Wide Web. Now your Excel documents can come to life like they never did before.
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
Classes are held by a professional with real-life day to day advanced Excel experience. We try to concentrate our focus on training students to be ready for working environment.
At your office, home, conference room.. you name it. Training at your location, at your convenience.
Sessions can & will be catered to your particular needs.
Locations Serving: Washington, DC and surrounding areas.