Excel VBA unmerge columns automatically

This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column. You may also want to read: Excel VBA reformat merged cells to row Excel VBA separate line break data into different rows Excel VBA unmerge columns automatically For some systems, when you export a non-Excel report (e.g. PDF) to Excel format, some columns could be merged in order to fit the width of the original report layout. Even for Sharepoint, this formatting issue also happens. I...
More

Access VBA loop through Table records

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access. You may also want to read: Access VBA loop through all Tables using DAO.TableDef Access VBA loop through Table records Suppose we have an Acess Table or Query and you want to loop through the records (in Excel terms, to loop the values in each row), we have to make use of Recordset objects, which has different Methods to help us manipulate data in a database at the record level. I...
More

Excel delete duplicated data in consecutive rows

This Excel tutorial explains how to delete duplicated data in consecutive rows (delete the value, not delete row). You may also want to read: Excel delete blank rows Remove duplicates in text Excel delete duplicated data in consecutive rows In conventional Pivot Table layout (Tabular), all data are grouped together and duplicated data would not repeat. Division Department Div1 Dept1 Div2 Dept2 Dept3 Dept4 Div3 Dept5 Dept6   ...
More

Create Cartesian product with Excel Query

This Excel tutorial explains how to create Cartesian product with Excel Query. You may also want to read: Microsoft Access create Cartesian product with Cross Join Create Excel Query and update Query Create Cartesian product with Excel Query In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cart...
More

Create Excel Query and update Query

This Microsoft Excel tutorial explains how to create Excel Query, create Join Table, update Query, add Query criteria. Microsoft Excel create Query Similar to Microsoft Access Query, Excel allows users to create Query through graphical user interface, which means you don't need to have technical skills to write any SQL statement. Although Microsoft Excel has the capability to do that, Access undeniably provides a much easier way to build Query because You can create Expression and apply c...
More

Microsoft Access produce Cartesian product with Cross Join

This Microsoft Access tutorial explains how to produce Cartesian product with Cross Join in Access Query. You may also want to read: Create Cartesian product with Excel Query Microsoft Access Cartesian product with Cross Join In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most...
More

Excel VBA use Inputbox to select options

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE. Excel VBA use Inputbox to select options In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case. In the below example, I create three options in the Inputbox for users to select: 1. Mary 2. May 3. Susan If users select something else, a...
More

Solution to Access Error 3047 Record is too large

This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method. Access Error 3047 Record is too large Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database. According to Access 2016 specifications , Access Table is limited to 4000 records, and the total size of Access is 2GB. If you create a Table that contains more than 4000 records, ...
More

Access replace Crosstab Query with Expression

This Microsoft Access tutorial explains how to replace Crosstab Query with Expression in order to add multiple aggregate values. Access replace Crosstab Query with Expression In previous post, I have introduced how to use Crosstab Query to build a matrix report. However, Crosstab Query has a limitation that it can only add one aggregate value in the report, which is the Sum of amount in the above example. I also don't like the fact that I need to define the order of the month in the Pro...
More

Excel delete all comments in Workbook

This Excel tutorial explains how to delete all Comments in Workbook using VBA and without using VBA. You may also want to read: Excel show all comments in worksheet Excel consolidate all Comments in the workbook Excel - delete all Comments in Worksheet In Excel spreadsheet, if you know where the comments locate, you can delete multiple Comments by selecting multiple Cells, then right click on any selected Cells > select Delete Comment.   If you are not certain where ...
More