Excel Workshop Part 1

Data in a Column to a Single Cell Separated by Commas – Macro VBA

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

 

Preferred Results:

43680;43681;43659;43677;43578;43522

 

Excel Macro:

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
Excel Workshop Part 1

From Several Columns to a Single Column with a Formula

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

Preferred Results:

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

Excel Formula:

$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)

Excel Workshop Part 1

Add a New WorkSheet in Excel – VBA Macro Code

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

Sub AddNewWorksheet()

 Dim NewWorkSheet As Worksheet
 Set NewWorkSheet = Sheets.Add

End Sub
Excel Workshop Part 1

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

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
Excel Workshop Part 1

Delete All Hidden Columns and Rows – VBA Excel – Code

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