Excel VBA set print area

This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA. Excel VBA set print area To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property. For example,  in order to set Print Area A1:N21, we can write Public Sub printArea()     Active...
More

Excel VBA freeze panes using FreezePanes property

This Excel tutorial explains how to freeze panes in Excel spreadsheet and freeze panes in Excel VBA using FreezePanes property. Freeze Panes in Excel spreadsheet To explain how to freeze panes in Excel VBA, first I have to explain how to freeze panes in Excel spreadsheet. Freeze Row The most common kind of freeze panes is to freeze the first row as it contains the header of the column. So that when you scroll down the spreadsheet, you can still see the header in row 1. Highlight row 2, na...
More

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. 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. In this tutorial, I will demonstrate a standard Procedure to loop through Table records....
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