#COGNOS ANALYTICS MERGING CELLS IN EXCEL UPDATE#
#COGNOS ANALYTICS MERGING CELLS IN EXCEL CODE#

' if this gives you an error, make sure that there an ampersand (&) sign, rather than the & literal string. 'count the number of rows in the new worksheetĪpplication.ScreenUpdating = True Exit Sub Cells(lNoofRows, lLoopCounter)).Copy Destination:=shtNew.Range(shtNew.Cells(lCountRows + 1, 1), shtNew.Cells(lCountRows + lNoofRows, 1)) 'Count the number of rows in the looping column 'Replace. 'Start a loop to copy and paste data from the first column to the last column For lLoopCounter = 1 To lNoofCols Try again', vbInformation, 'Sheet Exists' Exit Sub End If With shtOrg Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sNewShtName 'Add a new worksheet, rename it and set it to a variable If Not SheetExists(sNewShtName) Then 'Set a sheet variable for the sheet where the data resides Set shtOrg = ActiveSheet SNewShtName = InputBox( 'Enter the new worksheet name', 'Enter name', 'newsht')

#COGNOS ANALYTICS MERGING CELLS IN EXCEL UPDATE#

'Turn off the screen update to make macro run fasterĪpplication.ScreenUpdating = False 'Ask for a new sheet name, if not provided use newsht See the screen shots for example.ĭim lNoofRows As Long, lNoofCols As Long Dim lLoopCounter As Long, lCountRows As Long Dim sNewShtName As String Dim shtOrg As Worksheet, shtNew As Worksheet

If everything went well, you should have a new worksheet with all the data from original worksheet in one column with the column headers.

Enter the new worksheet name in the input box.

Select the Stack_cols procedure and hit run.

Press Alt + F8 to bring the macro window.

Insert a new module in your workbook using the Insert menu option from the toolbar and clicking Module.

Press Alt + F11 to bring up the VBA editor.

#COGNOS ANALYTICS MERGING CELLS IN EXCEL CODE#

This code also includes a function to check whether a worksheet exists.

The following procedure will help you do so. The result would be: A1 will contain Bob, A2 will contain Jane, A3 will contain Mary, A4 will contain Sue. For example, if cell A1 has the value of Bob, A2 has Jane, and B1 has Mary and B2 has Sue, you may want to collapse all the values from both A and B columns into one column. In Excel, from time-to-time you may want to stack different columns of data into one column.

Cognos analytics merging cells in excel