VBA Excel Access roundup rounddown Function

VBA Excel Access roundup rounddown Function
Posted on
This Excel Access tutorial explains how to use VBA to write an Access roundup (round up) and Access rounddown (round down) Function to simulate that in Excel. These roundup and rounddown Functions can also be used in Access and Excel VBA. Excel Access roundup rounddown Function In Excel Worksheet Function, there are round, roundup, rounddown, mround Functions. In Excel VBA, there is only roun...
More

Access Excel VBA generate random password or random characters

Access Excel VBA generate random password or random characters
Posted on
This Access Excel VBA tutorial explains how to generate random password or random characters with random number, random letters and random symbols. You may also want to read: Excel VBA generate non-duplicated random number Excel VBA Rnd Function to generate random number Access Excel VBA generate random password or random characters If you are a system administrator, very likely you n...
More

Excel automatically select specific columns using Custom Views and Query

Excel automatically select specific columns using Custom Views and Query
Posted on
This Excel tutorial explains how to select specific columns in a worksheet with many columns using Custom Views and Query. You may also want to read: Create Excel Query and update Query Excel automatically select specific columns using Custom Views and Query Many people including myself like creating a master report with many columns and then send it to users for them to manually select co...
More

Excel find the percentile of a number using PERCENTRANK.EXC function

Excel find the percentile of a number using PERCENTRANK.EXC function
Posted on
This Excel tutorial explains how to find the percentile of a number from an array using PERCENTRANK.EXC function. Excel Percentile Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the loc...
More

Excel VBA copy contents of protected worksheet

Excel VBA copy contents of protected worksheet
Posted on
This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet. You may also want to read: Excel VBA protect worksheet with password Excel VBA hide worksheet with password (xlVeryHidden) Copy contents of protected worksheet Some authors would protect worksheets with password so that other people cannot modify and not even able t...
More

Excel VBA convert date period to date row

Excel VBA convert date period to date row
Posted on
This Excel VBA tutorial explains how to convert date period to date row. Excel VBA convert date period into date row Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period Begin Date End Date 1/1/2018 1/3/2018 However some system store one date per row. ...
More

Excel VBA filter value then copy data to new worksheet

Excel VBA filter value then copy data to new worksheet
This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data to new workbook. You may also want to read: Excel VBA copy each worksheet to new workbook Access VBA auto generate mass report by group to Excel Filter value then copy data to new worksheet I find it very difficult to come up with a suitable title and key words fo...
More

Access Excel VBA sort Array items

Access Excel VBA sort Array items
Posted on
This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order. You may also want to read: Use VBA Excel Function to sort data in ascending order Access Excel VBA sort Array items In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, C...
More

Excel check duplicate values using Conditional Formatting

Excel check duplicate values using Conditional Formatting
Posted on
This Excel tutorial explains how to check duplicate values in Excel spreadsheet by highlighting duplicate values using Conditional Formatting. You may also want to read: Excel assign sequence number to duplicate records Excel delete duplicated data in consecutive rows Excel check duplicate values Back in Excel 2003, it was a pain to identify duplicate values. What I used to do was to sort...
More

Excel convert data from column to row

Excel convert data from column to row
This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row). You may also want to read: Excel VBA consolidate multiple workbooks into one workbook Excel VBA combine worksheets columns into one worksheet Excel convert data from column to row Different database have different structure. Some database put similar data in the same column wi...
More