Split String into Alphabets and Digits - Type 1

Description :

Here's an example of a User Defined Function for extracting Alphabets and Digits from any given string.

Code :

Option Explicit

Public Function splitString(ByVal inputStr As String, ByVal outputType As Integer)
    Dim i As Long
    Dim numPart As String
    Dim strPart As String
    
    ' if outputType = 0 then display digits
    ' if outputType = 1 then display Alphabets
    
    For i = 1 To Len(inputStr)
        If Asc(Mid(inputStr, i, 1)) > 47 And Asc(Mid(inputStr, i, 1)) < 58 Then
            numPart = numPart & Mid(inputStr, i, 1)
        ElseIf Asc(Mid(inputStr, i, 1)) > 64 And Asc(Mid(inputStr, i, 1)) < 91 Then
            strPart = strPart & Mid(inputStr, i, 1)
        ElseIf Asc(Mid(inputStr, i, 1)) > 96 And Asc(Mid(inputStr, i, 1)) < 123 Then
            strPart = strPart & Mid(inputStr, i, 1)
        End If
    Next i
    
    If outputType = 0 Then
        splitString = numPart
    ElseIf outputType = 1 Then
        splitString = strPart
    End If

End Function

Output :

input	digits	string
sdfds3243	3243	sdfds
sefd323	323	sefd
sfd 32423	32423	sfd
fds  4323	4323	fds
f g j  6 899  9	68999	fgj

input	digits	string
sdfds3243	=splitString(A2,0)	=splitString(A2,1)

Example File Link :

Example File

Extracting Output in Other Format Type 1

If your have data in the given structure and you want it to restructure your data into a given format then you can use this code to restructure and format your new data block.

Input data Structure:

Output Data Structure:

Code :

Option Explicit

Sub split()
    
    Dim i, j, k, splitRow, splitCol, blockCount, columnCount, segmentCol As Long
    Dim dataBlock(500) As String
    Dim dataBlockCol(500) As Integer
    
    Dim summaryRow, countGFS As Integer
    summaryRow = ((Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 2) * 2) + 1
    
    splitRow = 2
    splitCol = 1
    segmentCol = 1
    blockCount = 0
    Sheet2.Cells.Clear
    
    'finding data names and location of individual data blocks
    For i = 2 To Sheet1.UsedRange.Columns.Count
        If Sheet1.Cells(1, i).Value <> "" Then
            blockCount = blockCount + 1
            dataBlock(blockCount) = CStr(Sheet1.Cells(1, i).Value)
            dataBlockCol(blockCount) = i
        End If
    Next i
    
    countGFS = 2
    'spliting cell contents
    For j = 1 To blockCount
        Sheet2.Cells(1, splitCol).Value = dataBlock(j)
        
        Sheet2.Cells(summaryRow - 1, splitCol).Value = "-"
        Sheet2.Cells(summaryRow, splitCol).Value = dataBlock(j)
        Sheet2.Cells(summaryRow + 1, splitCol).Value = "ASDF"
        
        columnCount = 0
        For i = 3 To Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 1

            Sheet2.Cells(1, splitCol + 1).Value = "Col" & CStr(columnCount)

            Sheet2.Cells(splitRow, segmentCol).Value = Sheet1.Range("A" & CStr(i)).Value
            Sheet2.Cells(splitRow, splitCol + 1).Value = Sheet1.Cells(i, dataBlockCol(j) + 4).Value
            Sheet2.Cells(splitRow + 1, segmentCol).Value = Sheet1.Range("A" & CStr(i)).Value
            Sheet2.Cells(splitRow + 1, splitCol + 1).Value = Sheet1.Cells(i, dataBlockCol(j) + 5).Value
            
            Sheet2.Cells(summaryRow - 1, splitCol + 1).Value = "-"
            Sheet2.Cells(summaryRow, splitCol + 1).Value = Sheet1.Cells(2, countGFS).Value
            Sheet2.Cells(summaryRow + 1, splitCol + 1).Value = Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row, countGFS).Value
            
            splitRow = splitRow + 2
            splitCol = splitCol + 1
            columnCount = columnCount + 1
            countGFS = countGFS + 1
            
            If i = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row - 1 Then
                Sheet2.Cells(summaryRow, splitCol + 1).Value = Sheet1.Cells(2, countGFS).Value
                Sheet2.Cells(summaryRow + 1, splitCol + 1).Value = Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row, countGFS).Value
            End If
            
            
        Next i
        
        countGFS = countGFS + 1
        splitRow = 2
        splitCol = Sheet2.UsedRange.Columns.Count + 4
        segmentCol = splitCol

    Next j
    
    'copying formatting
    Sheet2.Select
    For j = 1 To blockCount
        If j = 1 Then
            Range("A1").Select
        Else
            Cells.Find(What:=dataBlock(j), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
            , SearchFormat:=False).Activate
        End If
        
        
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheet1.Select
        Cells.Find(What:=dataBlock(j), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        Selection.Copy
        Sheet2.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        
        Selection.End(xlDown).Select
        Selection.Copy
        Selection.End(xlToRight).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(-1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Range("A1").Select
        Application.CutCopyMode = False
    Next j
    
    Rows(summaryRow - 1).Clear
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    
    Sheet1.Select
    Range("A1").Select
    
    Sheet2.Select
    MsgBox "Split complete"
    
End Sub

Explanation :

In the above mentioned code
Sheet1 is the Input Data Sheet
Sheet2 is the Output Data Sheet

Example File link :

Example File

Update Pivot Table using VBA (Change Data Source and Add New Data into Pivot)

If you have a data set in which a new column is added at a specified time interval then refreshing the pivot every time becomes monotonous.

Excel macro can be used to automate this process.

1. Changing the old data source and adding the new column into the data set.

2. Manipulating the table structure and and adding the new columns into 

the rows or columns or filters or values.

3. Refreshing the table structure with the new data.

Given below is the code to do the same.

1. Adding the new column in values section

Sub()


Sheet1.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw data!R3C1:R21C" & CStr(Sheet2.UsedRange.Columns.Count) _
        , Version:=xlPivotTableVersion14)
    
    Sheet1.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(fieldName), "Sum of " & fieldName, xlSum

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

    ActiveWorkbook.RefreshAll


The new field can also be added to the xlRowField, xlColumnField and xlPageField

Please find example file here.
https://drive.google.com/file/d/0B20WcznMWRTfMzBnd29JeG9LalE/view?usp=sharing

Dynamic table with vertical and horizontal scrolling options in Excel

In this article we will go through adding a dynamic grid view type table structure with vertical and horizontal scroll bar control

Sometimes we might come across a situation where we have to display an entire dataset by using both horizontal and vertical scroll bar control. Take for example if we have to display student records for a given class consisting of more than 100 students and also you just have a display window of 20 rows and 4 columns at a time.

The simplest options to do this would be create a table like structure by using cell formatting and then populate the inner cells by 20 rows of data. The data population would be triggered by the changing of the scroll bar control.

Since there is no grid view control in MS Excel and we are just dealing with data filled worksheets instead of databases therefore this could be used as an easy and simple trick.

Step 1:

Arrange all your data into a sheet in the same table structure as you would want to display in the table.

We have used a dataset of 151 rows, consisting of demo student details spread across 11 columns in a single datasheet.

Step 2:

In another worksheet, use cell formatting with background colors to prepare a table like structure. Keep exact number of blank rows for data you would like to show.

Step 3:

Go to developer tab and add a vertical and horizontal scroll bar ActiveX Control. Set the min, max and value properties of the scroll bar controls while workbook_open event triggers

tool is the object name of the worksheet that contains the table structure. data is the object name of the worksheet that contains the data structure. usedrange gets the number of cells that contains data. sb_horizontal is the object name of the horizontal scroll bar control. sb_vertical is the object name of the vertical scroll bar control.

For the vertical scroll bar control; set the min value as the row number of the 1st row of your dataset. The max value should be last row number of your dataset minus the number of rows in the table structure.

For the horizontal scroll bar control; set the min value as the column number of the 1st column of your dataset. The max value should be last column number of your dataset minus the number of columns in the table structure.

The max value is this because we would want the table to display till the last row and column when the scroll bars are at their bottom most position.

Every instance of scroll bar change results in displaying 20 rows and 4 columns (number of blank rows and columns in the table structure)

Step 4:

Open the VBA window and in the change event of the activex scroll bar control write the following lines of code.

For the horizontal scroll bar control

1. Range H28 and K28 will contain the column reference of the first and last column position set through the horizontal scroll bar control.
2. Then the heading of the dataset is copied from the data sheet and pasted in the set range H4:K4
3. And the vertical scroll bar’s change event is called to refresh the data structure.

For the vertical scroll bar control

1. CStr(Mid(tool.Range("H28").Value, 2, 1) takes the column reference value from the H28 cell
2. CStr(Mid(tool.Range("K28").Value, 2, 1) takes the column reference value from the K28 cell
3. CStr(sb_vertical.Value + 20) for selecting plus 20 rows from the initial row number
4. Range H5:K25 is the range which is used as the reference to where the data is to be pasted

Step 5:

Save the file as required and then click scroll bar to see the effect.

Example file link:

https://drive.google.com/file/d/0B20WcznMWRTfV3p5bGx1Q3dsRk0/view?usp=sharing

Trick to make a dynamic table in MS Excel

In this article we will go through adding a dynamic grid view type table structure with scroll bar control

Sometimes we might come across a situation where we have to display an entire dataset by using a scroll bar control. Take for example if we have to display student records for a given class consisting of more than 100 students and also you just have a display window of 20 rows at a time.

The simplest options to do this would be create a table like structure by using cell formatting and then populate the inner cells by 20 rows of data. The data population would be triggered by the changing of the scroll bar control.

Since there is no grid view control in MS Excel and we are just dealing with data filled worksheets instead of databases therefore this could be used as an easy and simple trick.

The task can be done by following the given easy to understand steps.

Step 1:

Arrange all your data into a sheet in the same table structure as you would want to display in the table.

I have used a dataset of 151 rows, consisting of demo student name, roll, etc in a single datasheet.

Step 2:

In another datasheet, use cell formatting with background colors to prepare a table like structure.

Keep exact number of blank rows for data you would like to show.

Step 3:

Go to developer tab and add a scroll bar ActiveX Control and set its properties, i.e., min max values.

Set the min value as the row number of the 1^st row of your dataset. The max value should be last row number of your dataset minus the number of blank rows in your table structure.

The max value is this because we would want the table to display till the last row when the scroll bar is at the bottom most position.

Every instance of scroll bar change results in displaying 20 rows (number of blank rows)

Step 4:

Open the VBA window and in the change event of the activex scroll bar control write the following lines of code.

• selectColRange = "A:D"
• Set copyRange = data.Range(selectColRange)
• copyRange.Range("A" + CStr(ScrollBar1.Value) + ":D" + CStr(ScrollBar1.Value + 20)).Copy ThisWorkbook.Sheets("Summary").Range("C7")

The 1^st line is used for declaring the column range which you want to display

The 2^nd line is used for setting the copy range. Here data is the name of the sheet where we have our dataset. If you didn’t set the data sheet name then data object name can be replaced with Sheets(“<Sheet Name>”).

The 3^rd line is used for setting the copy range in the dataset.

• ("A" + CStr(ScrollBar1.Value) gives the 1^st cell coordinate of the copy range.
• D" + CStr(ScrollBar1.Value + 20) gives the last cell coordinate of the copy range.
• ScrollBar1.Value + 20 is used because we want a set of 20 rows to be displayed in the table structure.
• ThisWorkbook.Sheets("Summary").Range("C7"): The table structure is on Summary sheet. And Range C7 is the cell coordinate where the copy range block will be copied to.

Step 5:

Save the file as required and then click scroll bar to see the effect.

Example File link:
https://drive.google.com/file/d/0B20WcznMWRTfcVRWTjJKODRZbFk/view?usp=sharing