Sunday, May 9, 2021
Home Tags Vba

Tag: vba

Split Row Into Multiple Rows

For this request we needed to loop through a number of rows and where candidate data was separated by a comma split the text into however many separate values are found between the comma’s. As part of the split process to also divide an amount by the number of candidates to be split

Filtering Dates in Multiple Columns

I am in need of a way to filter the information in the attached file. I need to be able to filter the entire sheet to show all row's that have a specified month/year no matter which column the specified month/year is in. This is used for my monthly report to show all after sales activity for each month. For example, at the end of the month I need to be able to filter all activities for the month of May 2014 to show all emails, phone calls, start-ups, acct notify, one year follow ups so I can convert that to PDF and send to the CEO

Split Function

The Split function is a VBA function that is used for breaking strings into an array. The separation of string is identified by indicating a character or set of characters which are called delimiters. This function comes in handy, for example, when you have a list of whole names of people in a column of your worksheet and you want to split them in order to get the first and last names of each person.

Highlight a Row based on Values in the Row

Request was received to highlight rows dependent on values in specific columns of data. A macro was developed to run through the worksheet and set a value and format for the rows based on the compared columns of information.

CTRL+F1 to Hide the Excel Toolbar using VBA

There are a number of options to solve this problem, the simplest approach we found for this request was the combination of the "SendKeys" function and the Workbook_Activate procedure. Basically we created a routine that would Hide the Toolbar each time the workbook was activated. Then when the workbook was closed or de-activated it would reset the Toolbar to the original state.

Transfer most Recent Records

A macro developed to compare rows of data and then transfer unique rows from the source worksheet into a result worksheet. This can be applied across many different uses in other spreadsheets.

Extract Text from String – using MID Function

For this problem we received a sample workbook. We then used VBA to create a custom function to extract the strings from the starting value and display in a separate column. This function can be re-used for many other types of extract and display

Protect VBA

You can protect you complex VBA scripts from modification and copying through the use of password protection. In this post we will demonstrate how to use the standard Excel security to protect you VBA scripts and macros.

Open a Workbook

The following routine will Open a Workbook and create a reference to it for use in further processing

Extract Workbook Name from Full Filename

If you only have a full filename reference to a workbook the following macro can be used to extract the Workbook name. This can be useful if you want to test if a Workbook is already open before attempting to open it. This is good practice and avoids receiving the standard Excel warning messages if you attempt to open a Workbook that is already open.