Data in a Column to a Single Cell Separated by Commas – Macro VBA
/in Excel, Learn/by adminThis 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
From Several Columns to a Single Column with a Formula
/in Excel, Learn/by adminThis 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)
Add a New WorkSheet in Excel – VBA Macro Code
/in Excel, Learn/by adminThis 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 Excel, Learn, Office/by adminIf 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
Delete All Hidden Columns and Rows – VBA Excel – Code
/in Excel, Learn/by adminThis 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
Contact Us
200 W Madison St
Suite 2100
Chicago, IL 60606
Please, feel free to call us anytime.
Locations Serving: Chicago, North, North-East, North-West and West Suburbs, including Northbrook, Schaumburg, Hoffman Estates and Naperville IL.