How to manipulate data in Excel: VBA

Microsoft Excel is a powerful tool that can be used for data manipulation. To make the most of the software, you need to use VBA. Visual Basic for Applications, or VBA, allows Excel users to create macros, which are powerful time-saving custom functions for data manipulation and analysis. Macros process VBA code to manage large data sets that would otherwise take a lot of time to modify.

VBA example script used in Excel

Sub ConfigureLogic()
Dim qstEntries
Dim dqstEntries
Dim qstCnt, dqstCnt
qstEntries = Range("QualifiedEntry").Count
qst = qstEntries - WorksheetFunction.CountIf(Range("QualifiedEntry"), "")
ReDim QualifiedEntryText(qst)
'MsgBox (qst)
dqstEntries = Range("DisQualifiedEntry").Count
dqst = dqstEntries - WorksheetFunction.CountIf(Range("DisQualifiedEntry"), "")
ReDim DisqualifiedEntryText(dqst)
'MsgBox (dqst)
For qstCnt = 1 To qst
QualifiedEntryText(qstCnt) = ThisWorkbook.Worksheets("Qualifiers").Range("J" & 8 + qstCnt).value
    'MsgBox (QualifiedEntryText(qstCnt))
    logging ("Configured Qualified Entry entry #" & qstCnt & " as {" & QualifiedEntryText(qstCnt) & "}")
Next
For dqstCnt = 1 To dqst
DisqualifiedEntryText(dqstCnt) = ThisWorkbook.Worksheets("Qualifiers").Range("M" & 8 + dqstCnt).value
   'MsgBox (DisqualifiedEntryText(dqstCnt))
   logging ("Configured DisQualified Entry entry #" & qstCnt & " as {" & DisqualifiedEntryText(dqstCnt) & "}")
Next
includeEntry = ThisWorkbook.Worksheets("Qualifiers").Range("IncludeSibling").value
    'MsgBox (includeEntry)
    logging ("Entrys included in search - " & includeEntry)
End Sub

How to analyze and manipulate entries in a spreadsheet?

To use VBA for data analysis, you will need to check the settings in Excel for the Developer tool. To find it, locate the Excel Ribbon and search for the Developer tab. You will need to activate it in the Excel Settings menu if it is not displayed.

Next, create a new worksheet and name it "Qualifiers." We will use this sheet to check for everything that qualify the selections.

Next, set up the qualifiers on the sheet according to the code. It must be entered manually; cut and paste will not work.

ThisWorkbook.Worksheets("Qualifiers").Range("J" & 8 + qstCnt).value

How to locate the range and construct an array?

The range in the function above is cell J9. The range function notes an 8; however, the actual range is 9 because:

For qstCnt = 1 To qst

The above statement starts at 1, not 0. Therefore, the list starts at 9. In this case, note (qstCnt=1).

To construct an array out of entries on the Qualifiers worksheet, place random words in cells J9-J13. Once the rows are completed, we can move forward with finding and manipulating data in Excel.

Private Sub CountSheets()
Dim sheetcount
Dim WS As Worksheet
sheetcount = 0
logging ("*****Starting Scrub*********")
For Each WS In ThisWorkbook.Worksheets
    sheetcount = sheetcount + 1
If WS.Name = "Selected" Then
                                'need to log the date and time into sheet named "Logging"
                                ActionCnt = ActionCnt + 1
                                logging ("Calling sheet: " & WS.Name)
                                scrubsheet (sheetcount)
                                Else
                                ActionCnt = ActionCnt + 1
                                logging ("Skipped over sheet: " & WS.Name)
                                End If
Next WS
'MsgBox ("ending")
ActionCnt = ActionCnt + 1
logging ("****Scrub DONE!")
Application.ScreenUpdating = True
End Sub

There is an example of a working tab counter.

Dim sheetcount
Dim WS As Worksheet
sheetcount = 0
logging ("*****Starting Scrub*********")
For Each WS In ThisWorkbook.Worksheets
    sheetcount = sheetcount + 1

After initialising the sheet count, set it to 0 in order to restart the counter.

Logging() is another subroutine that keeps track of all actions in order to audit selections.

The next For loop sets up the Active Workbook for counting. WS is the initialised and ThisWorkbook. Worksheets is the active tab in the book. Since we have not named the workbook, this module will run on any active workbook. If you are working on multiple workbooks and have the wrong one activated, it will attempt to run on it. To avoid errors, take precautions to name your specific workbook or only work on one at a time.

Every time the loop fires, it adds one variable to the sheet count to keep track of the number of tabs. Then we move to:

If WS.Name = "Selected" Then
                                'need to log the date and time into sheet named "Logging"
                                ActionCnt = ActionCnt + 1
                                logging ("Calling sheet: " & WS.Name)
                                scrubsheet (sheetcount)
                                Else
                                ActionCnt = ActionCnt + 1
                                logging ("Skipped over sheet: " & WS.Name)
                                End If

Here, we look for the Selected tab.

If the variable WS equals Selected, we log it and fire the subroutine Scrub Sheet. If the variable WS is not equal to Selected, it is logged that that sheet was skipped and the action is counted. The above code is an example of how to count the number of and locate a particular tab.

The following list is all of the different methods that can be used to manipulate data!

Have FUN!

How to count the number of sheets in a workbook?

 Dim TAB
                For Each TAB In ThisWorkbook.Worksheets
                'some routine here
                next

How to find the last row, column, or cell on a worksheet?

Dim cellcount
                cellcount = Cells(ThisWorkbook.Worksheets("worksheet").Rows.Count, 1).End(xlUp).Row

How to filter by using advanced criteria?

 Range("A2:Z99").Sort key1:=Range("A5"), order1:=xlAscending, Header:=xlNo

How to apply auto-fit property to a column?

Columns("A:A").EntireColumn.AutoFit

How to get values from another worksheet?

 dim newvalue
                newvalue = ThisWorkbook.Worksheets("worksheet").Range("F1").value

How to insert a column into a worksheet?

Dim Row, Column
                Cells(Row, Column).EntireColumn.Select
                Selection.Insert

How to insert multiple columns into a worksheet?

 Dim insertCnt
                                Dim Row, Column
                                For insertCnt = 1 To N
               ThisWorkbook.Worksheets("worksheet").Select
               Cells(Row, Column).EntireColumn.Select
               Selection.Insert
               Next

How to add a named range to a particular sheet?

 ThisWorkbook.Worksheets("worksheet").Names.Add Name:="Status", RefersToR1C1:="=worksheet!C2"
 

How to insert an entire row into a worksheet?

Dim Row, Column
                                Cells(Row, Column).EntireRow.Select
               Selection.Insert

How to copy an entire row for pasting?

ActiveSheet.Range("A1").EntireRow.Select
                                Selection.Copy

How to delete an entire row?

ActiveSheet.Range("A1").EntireRow.Select
                                Selection.Delete

How to select a particular sheet?

 ThisWorkbook.Worksheets("worksheet").Select

How to compare values of a range?

                                Dim firstrange
                                Dim Logictest
                                Logictest = "some word or value"
                                If (Range(firstrange).value = Logictest) then
                                'some routine here
                                End If