Setting Chart Position (VBA)

After the chart is generated, it will appear at random place in your worksheet. How do I place the generated chart into a specified position in excel. And this post will guide you how to write down an excel VBA Macro to set chart absolute position in your worksheet.

Setting Chart Position

Assuming that you have create a chart and it is placed at random position by default. Of course, you can move the chart to any position by dragging the chart. And if you want to move the chart to a certain position via VBA, just do the following steps:

#1 select the chart that you want to set position.

setting chat position11

2# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

3# then the “Visual Basic Editor” window will appear.

4# click “Insert” ->”Module” to create a new module.

convert column number to letter3

5# paste the below VBA code into the code window. Then clicking “Save” button.

setting chat position1

6# back to the current worksheet, then run the above excel macro. Click Run command.

setting chat position2

7# Let’s see the result.

setting chat position3

Fill Blank Cells with specific value (0 or null)

This post will teach you how to fill all blank cells with specific value, such as: 0 or any other values in excel. How to locate all blank cells and then fill in all the blanks with any specific value. The below will guide you to fill empty cells with different ways in excel 2016 or 2013. How to fill blank cells with VBA code in Excel.

Assuming that you have a list of date in your worksheet and contains hundreds of empty or blank cells, and you want to fill the blank cells with a specific value, such as: 0 or zero. There are different methods to achieve the result. You can do it by manually, but it is not a good idea and it will consume a lot of time. Also you can refer to the below quick ways.

Method 1: Using Go to Special Feature

To fill all blank cells with specific value, you need to select or locate all blank cell in your worksheet firstly, you can use the Go To special Function to solve this problem in Microsoft excel. Let’s do the following:

1# select the range of cells that you want to fill in blanks

fill blank cells with specific value1

2# on the HOME tab, click Find & Select command under Editing group, then select Go To Special… menu from the pop-up menu list. Or just press F5 or Ctrl+G to open the Go To dialog box, then click Special… button.

fill blank cells with specific value2

3# the Go To Special window will appear on the screen.

4# choose Blanks radio button from the Select section. Then click OK button.

fill blank cells with specific value3

5# you will see that all blank cells are highlighted in the selected range.

fill blank cells with specific value4

6# press F2 to enter one specific value, such as: 0 or null in the active cell

fill blank cells with specific value5

7# then press Ctrl + Enter shortcuts, and you will see that all empty cells will be filled with the value that you entered in the step 6.

fill blank cells with specific value6

Method 2: Using Find & Replace Feature

You can also use the Find & Replace feature to fill in blank cells in excel. Let’s refer to the following steps:

1# select the range of cells with blank cells

2# On the HOME tab, click on “Find & Select” command under Editting group.

3# the Find and Replace dialog box will appear on the screen.

4# switch to Replace tab in the dialog, then leave the Find what: box blank and enter 0 or any other value in Replace with: box.

fill blank cells with specific value7

5# click Replace All button. And click on the OK button again. You will find that all blank cells will be filled with the value you entered in the Replace with box.

fill blank cells with specific value8

Method 3: using Excel Macro to Fill Blank cells

If you want to fill in the blank cells quickly with VBA code, then you can do it following:

1# select the range of cells that contains blank cells

2# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

3# then the “Visual Basic Editor” window will appear.

4# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

fill empty blank cell with value2

Sub FillEmptyBlankCellWithValue()
    InputValue = InputBox("Type a value that you want to fill blank cell:", "FillEmptyBlankCellWithValue")
    For Each cell In Selection
        If IsEmpty(cell) Then
            cell.Value = InputValue
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

fill blank cells with specific value10

6# Enter one value that you want to fill blank cells, such as: type “excelhow.net” text string.

fill empty blank cell with value1

7# Let’s see the last result:

fill blank cells with specific value12

So you can use the above three quick ways to fill blank cells with 0 or any other value in excel. Welcome any comments.

Delete Rows Based on Cell Value

This post will guide you how to use the Find & Replace feature to delete or remove all rows based on certain cell value in Microsoft Excel. Or how to delete all rows that contain certain value with VBA code in excel, such as, removing all rows if cell contains 0 or any other value.

Assuming that you have a worksheet and you want to delete rows based on cell vlaue if the value is equal to 0. In another word, if the cell value contain 0, then delete that row contain value 0. The first thing you need to do is that how to find rows that contain certain value in your worksheet. And how to solve this problem? You can use the Find & Replace command or VBA code to achieve the result.

Delete rows based on cell value with Find & Replace feature

To delete rows based on a certain cell value with Find & Replace feature, you can refer to the following steps:

1# Select the range of cells that you want to delete rows based on certain cell value.

delete rows based on cell value1

2# on the HOME tab, click Find & Replace command under Editing group. Or just press Ctrl +F shortcut to open the Find and Replace box.

delete rows based on cell value2

3# the Find and Replace dialog box will appear on the screen.

4# type the certain value in the Find what: text box, and then click Find All button

5# you will see that the searched result will appear in the Find and Replace window.

delete rows based on cell value3

6# select all found values in the Find and Replace window and you will see that all cells contain certain value will be highlighted in your selected range.

delete rows based on cell value4

7# right-click on the selected cells, and select Delete…menu from the drop-down menu list. Then choose Entire row radio button in the Delete dialog box. Click OK button.

delete rows based on cell value5

delete rows based on cell value6

delete rows based on cell value7

Or you can go to HOME tab, click Delete command. Then click Delete Sheet Rows.

So far, all rows contain the certain value are deleted in your selected range. And if you want to remove the columns base on the certain value, just select Entire column radio button in the step 7.

Delete Rows Based on cell value with Excel VBA Macro (VBA code)

If you want to delete rows that contain the certain value in your worksheet with excel VBA Marco, you can refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove rows based on values3

Sub RemoveRowsBasedValue()
    Dim Dr As Range
    Set Ip = Application.Selection
    Set Ip = Application.InputBox("Select one range that you want to remove rows:", "RemoveRowsBasedValue", Ip.Address, Type:=8)
    Ds = Application.InputBox("Please type a text string:", "RemoveRowsBasedValue", Type:=2)
    For Each R In Ip
        If R.Value = Ds Then
            If Dr Is Nothing Then
                Set Dr = R
            Else
               Set Dr = Application.Union(Dr, R)
            End If
        End If
    Next
    Dr.EntireRow.Delete
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

delete rows based on cell value9

6# select a range that you want to delete rows, click OK button. Then type a text string contained in the range of rows to delete.

remove rows based on values1

remove rows based on values2

delete rows based on cell value12

How to Add Prefix or suffix to Cell

This post will guide you how to add common prefix or suffix to all cells or the range of cells in your worksheet in Excel. How do I add the same prefix or suffix to a range of cells without any changing the existing numbers or text in the range. How to add prefix or suffix to all cells with Excel VBA Macro.

Assuming that you have a list of data that you want to add common text to the starting position of all cells, of course, you can do it by manually, but it will consume lots of time and it is not a good idea. And you can use the concatenate operator, the CONCATENATE function to create a new Excel formula to add prefix or suffix to the range of cells that you selected. Or you can write a new Excel VBA Macro to achieve the same result.

Method 1: Using Concatenation Operator to add prefix or suffix

If you want to add prefix or same text to the starting of all cells in your range, you can use the Concatenation operator to build a formula, for example, you want to add same text “excelhow” to all cells in range A1:A4, then you can use the following formula:

="excelhow"&A1

Ether this formula in a blank cell, such as: B1, then drag the AutoFill Handle down to other cells to apply this formula.

add prefix or suffix 1

If you want to add suffix to all cells with concatenation operator, you can use the following formula:

=A1&"excelhow"

add prefix or suffix2

You will see that all cells are added the same prefix text in your worksheet.

Method 2: Using concatenation function to add prefix

To add prefix or suffix text or number to the range of cells with concatenation function, you can wirte down the following formula:

=CONCATENATE("excelhow",A1)
or
=CONCATENATE(A1,"excelhow")

add prefix or suffix3

Method 3: Using Excel VBA Macro to add prefix

You can also use the following VBA Macro code to add prefix or suffix in your worksheet in Excel.  Let’s see the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

add prefix to cell113

Sub AddPrefixToCell()
    Set W = Application.Selection
    Set W = Application.InputBox("select one Range that you want to add prefix to each cell:", "AddPrefixToCell", W.Address, Type:=8)
    S = Application.InputBox("Please type a prefix character:", "AddPrefixToCell", "", Type:=2)
    For Each R In W
        R.Value = S & R.Value
    Next
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

add prefix or suffix5

6# select a range that you want to add prefix, click OK button, then add one prefix string or character. Click OK button.

add prefix to cell111

add prefix to cell112

7# you will see that the prefix is added to all range of cells.

add prefix or suffix8

If you want to add suffix to a range of cells with VBA macro in Excel, you can use the following VBA macro.

Sub AddSuffixToCell()
    Set W = Application.Selection
    Set W = Application.InputBox("select one Range that you want to add suffix to each cell:", "AddSuffixToCell", W.Address, Type:=8)
    S = Application.InputBox("Please type a prefix character:", "AddSuffixToCell", "", Type:=2)
    For Each R In W
        R.Value = R.Value & S
    Next
End Sub

add prefix or suffix9


Related Functions

  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…

Quickly Insert Multiple Rows or Columns in Excel

This post will guide you how to quickly insert multiple rows or columns in your Microsoft worksheet. You will see that how to add new multiple rows or columns with shortcut or insert feature, or insert copied cells in excel. Or how to quickly insert rows or columns with Excel VBA macro.

Suppose that you want to quickly insert 5 blank rows between row 4 and row5 in your worksheet, and how to do it? May be you can do it by manually, but this is not a good idea, because if you need to insert hundreds of blank rows or columns, it will be tedious and time-consuming.

Of course, there are several ways to quickly insert new multiple rows or column in Excel. Let’s see the below detailed description.

Method 1: Insert multiple rows or columns in Excel using Insert feature

Insert Rows:

If you want to insert 5 rows between Row 3 and Row 4 in your worksheet, then you can do it following steps:

1# you should locate the destination row, it should be Row 4, so select 5 entire rows below from Row 3 using the row number buttons.

insert multiple rows or columns 1

2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.

insert multiple rows or columns2

Or you can right-click on the selected rows, then choose the insert option from the pop-up menu list.

insert multiple rows or columns3

You will see that 5 rows are inserted after Row 3 in your worksheet.

Insert Columns:

If you want to insert 5 columns between column B and column C using insert feature, just do it follow:

1# select 5 columns next to column C (includes the column 3) using the column number buttons.

2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.

insert multiple rows or columns5 insert multiple rows or columns6

Method 2: Insert multiple rows or columns in Excel using shortcut

You can also use the shortcut F4 to insert multiple rows or columns in Excel, and this is the simplest way to insert new rows or columns in Excel.

Insert Rows:

If you want to insert 5 rows between Row 2 and Row 3, then you just need to select the Row 3, then press F4 shortcut 5 times, then it will insert 5 rows after Row 2.

Insert Columns:

If you want to insert 5 columns between Column B and Column C, then you just need to select the entire Column C, then press F4 shortcut 5 times. You will see that 5 new columns are inserted in your worksheet.

Method 3: Insert multiple rows or columns in Excel using Insert Copied Cells feature

You can also use the Insert Copied Cells functionality to inert multiple rows or columns, just follow the below steps:

1# select multiple rows in your worksheet (the selected rows should be equal to the number of rows that you want to inert)

2# press Ctrl +C to copy the selected rows

insert multiple rows or columns7

3# select the destination rows, such as, if you want to insert new rows between Row2 and Row 3, then you should select Row 3. Then right-click on it, choose Insert Copied Cells option from the drop-down menu list.

insert multiple rows or columns8

4# you will see that multiple rows are inserted after row 2.

insert multiple rows or columns9

And if you need to insert multiple columns using Insert Copied Cells method, just select multiple columns in the above Step1. Then repeat the rest steps.

Method 4: Insert multiple rows in Excel using Excel VBA macro

If you want to insert multiple rows with VBA macro in Excel, you can follow the below steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

insert multiple rows or columns10

Sub InsertRowsAtCursor()
    Answer = InputBox("How many Rows to insert? (50 Rows maximum)")
    NumLines = Int(Val(Answer))
    If NumLines > 50 Then
        NumLines = 50
    End If
    If NumLines = 0 Then
        GoTo EndInsertLines
    End If
    Do
        Selection.EntireRow.Insert
        Count = Count + 1
    Loop While Count < NumLines
    EndInsertLines:
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run button.

insert multiple rows or columns11

6# type rows number that you want to insert, then click OK button.

insert multiple rows or columns12

insert multiple rows or columns13

 

Split Excel workbook into separate Files

This post will guide you how to split a large workbook to separate files and each worksheet should be saved as a single file. How do I split the whole workbook into several excel worksheets or single excel file.

Assuming that you need to split your workbook that contains lots of worksheets into multiple single Excel file and its extension name is CSV.

The simplest way is to copy each worksheet to a single excel workbook or file by manually. So you can select one worksheet that you want to save as a single excel file, then create a new workbook, paste it in the new workbook. Then save it.

If you have hundreds of worksheet in your workbook, then it will be time-consuming and tedious. So do we have a quick way to split workbook to separate excel file? Of course yes, the below will describe one way to split your workbook with VBA code.

Split Excel workbook into separate files with VBA code

Let’s see the following steps:

If you want to quickly split a large workbook into several single excel file for each worksheet, then you can write a new Excel VBA macro code to achieve the result. And the below VBA code will save the separated excel file at the directory same as with the master workbook.

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

split workbook into separate file1

Sub SplitWorkbook()
    Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

5# back to the current worksheet, then run the above excel macro. Click Run command.

split workbook into separate file2 split workbook into separate file3

 

How to Get the List of File Names From a Folder

This post will teach you how to get the list of file names in a given directory to a worksheet in Excel. You will learn that how to use Excel to view Files and Folders in your worksheet by get the list of file names with different ways, such as: Excel VBA Macro, or FILES function.

If you want to get the list of file names from a folder that contain hundreds of files in it. You may be think the simplest way is that selecting the file and copying its name, then paste the file name in a cell in your worksheet, then repeat the above steps. If you follow this way to do it, you will take a long time to complete it. So it is a simple way, but it is also a huge waste of time. Do we have some quick way to achieve the same result? Of course yes, the below will talk two ways to copy the list of file names from a folder into worksheet quickly.

Method 1: Using FILES function to get list of file names

You can use the FILES function to get the list of file names from a directory, and this function is only works in name Defined Names. And then you can use a combination of the IFERROR function, the INDEX function and the Row function to create an excel formula. Let’s see the below steps:

1# Assuming that you want to get the list of file names from a directory, such as: C:\Users\devops\Tracing\WPPMedia\*, and enter this directory path into Cell B1.

get list of file names from a folder1

2# On the FORMULAS Tab, click Define Name command under Defined Names group, then select Define Names… from the drop-down menu list.

get list of file names from a folder2

3# the New Name window will appear. Set a name in the Name box, such as: FileNameList, type the following formula in the Refers to box.

=FILES(Sheet7!$B$1)

get list of file names from a folder3

4# enter the following formula in the Cell B3, then drag the AutoFill Handle down to others cells to apply this formula.

=IFERROR(INDEX(FileNameList,ROW()-2)," ")

get list of file names from a folder4

You will see that all file names from the specified folder are listed in your worksheet.

Method 2: Using File browser and web browser to get list of file names

If you want to use this method to get the list of file name from a directory, you need to install one web browser, such as: Firefox, Chrome or IE. Then do it follow steps:

1# Move to the destination folder or directory on the Windows File Explorer, then copy the path of that directory. Such as: C:\Users\devops\Tracing\WPPMedia

get list of file names from a folder5

2# open any web browser installed in your computer and paste the copied path in the address bar, then press Enter key.

3# you will see that it will add prefix file:/// at the beginning of the path in the address bar. And the file name list will be shown in the web page.

get list of file names from a folder6

4# press Ctrl + A shortcut to select all of file names in the web page and then press Ctrl +C to copy the selected content.

5# open your worksheet, then press Ctrl + V to paste all file names into your worksheet.

get list of file names from a folder7

Or you can save the web page in the Step 3 as the offline copy. Just press Ctrl + S or right-click on the web page, and then select Save Page As to save that web page.

Open the saved web page from the web browser, and copy its web address. Then click From WEB command under Get External Data group on the DATA tab in your worksheet.

get list of file names from a folder8

Paste the web address in Address box, then click Go button. Then click Import button. You will see that all files and folders details are imported to your worksheet.

Method 3: Using Excel VBA macro to get list of file names

You can write an excel VBA macro code to get the list of file names from a specified directory,

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

get list of file names from a folder9

Option Explicit
Sub GetFileNames()
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
    InitialFoldr$ = "C:\"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
     End With
End Sub

5# back to the current worksheet, then run the above excel macro.

get list of file names from a folder10

6# select a folder that you want to get all names, then click OK button.

get list of file names from a folder11


Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

Find Duplicate Values in Two Columns

This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so that the duplicate values can be highlighted or listed in a range of Cells.

Assuming that you need to compare two columns (Column A and Column B) to get the duplicate values in your worksheet, you can find duplicate values in two columns with Excel Formula, or Excel VBA Macro code.

Method 1: Find duplicate values in two columns with Excel Formula

To compare two given columns and find duplicate values in Excel, you can use a combination of the IF function, the ISERROR function, and the MATCH function to create an Excel formula. So you can use the following formula:

=IF(ISERROR(MATCH(A1,$B$1:$B$4,0))," ",A1)

Now you need to type this formula in Cell C1, press Enter key, drag AutoFill Handle down to Cell C2:C4, you will see all of the duplicated values are displayed in Column C.

find duplicate values in two columns1

Method 2: Find duplicate values in two columns with VBA Macro code

If you are familiar with the programming language and you can use a Visual Basic Macro to compare the value in two columns and then find duplicate values, just refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

find duplicate cells in two columns111

Sub FindUplicatesinTwoColumns()
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Select the first range in one column:", "FindUplicatesinTwoColumns", Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Select the second range in another column:", "FindUplicatesinTwoColumns", Type:=8)

    For Each R1 In Range1
        xValue = R1.Value
        For Each R2 In Range2
            If xValue = R2.Value Then
                If R3 Is Nothing Then
                    Set R3 = R1
                Else
                    Set R3 = Application.Union(R3, R1)
                End If
            End If
        Next
    Next
    R3.Interior.ColorIndex=3

End Sub

5# back to the current worksheet, then run the above excel macro.

find duplicate values in two columns3

6# Select the first range A1:A4 in Column A, click OK button, then select the second range B1:B4 in Column B, click OK button.

find duplicate values in two columns4 find duplicate values in two columns5

7# let’s see the result:

find duplicate values in two columns6

Method 3: Find duplicate values in two columns with Conditional Formatting feature

You can use conditional formatting with on a formula based on the COUNTIF function and the AND function to find the duplicate values in two specified columns and then highlighted them. Just do it following:

1# Select the entire Column A via click on the Column Header and then the column A will be highlighted

find duplicate values in two columns7

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

find duplicate values in two columns8

3# select Use a formula to determine which cells to format as Rule Type in the New Formatting Rule window

4# Type the following formula in the Format values where this formula is true: box

=COUNTIf($B:$B, $A1)

5# click the Format… button, then the Format Cells window will appear.

6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button.

7#you will be back to the New Formatting Rule windows and you can check a preview of the formatting you have selected. Then click OK button.

find duplicate values in two columns9

Then the conditional formatting rule will be applied to all values in two columns and highlighted the duplicate values.

find duplicate values in two columns9


Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

Related Posts

  • Highlight Rows
    You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
  • Find Duplicate Rows
    If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
  • Highlight Duplicate Rows
    this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
  • Highlight duplicate values
    this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……

Remove Leading and Trailing Spaces

In this tutorial you will learn that how to remove leading or trailing spaces from a text string in a cell in Excel. This post will guide you how to delete leading or trailing spaces of all cells in a column.

If you remove leading spaces in cells one by one by manually, it will waste a lot of time. So this post will show you two methods to quickly remove leading and trailing spaces from text in one or more cells in your worksheet.

Assuming that you need to remove all leading spaces and trailing spaces in the range of cells A1:A4, you can refer to the following two methods to achieve it.

Method 1: Remove Leading and Trailing spaces with Excel Formula

To remove leading and trailing spaces from text string in Cells, you can use the TRIM function to remove extra spaces. So you can type the following formula in Cell B1, then press Enter key.

=TRIM(A1)

Then select Cell B1 and drag the AutoFill Handle down to other cells that you want to remove the leading or trailing spaces.

You will see that all of your leading and trailing spaces in range A1:A4 are cleaned up.

remove leading and trailing spaces1

Note: if the text contains non-braking spaces, this formula will not work, because the TRIM function is not able to remove non-breaking spaces. At this time, you should remove the non-breaking spaces firstly with the SUBSTITUTE function, and then use the TRIM function to remove the rest leading and trailing spaces. So you can write down the following Excel formula:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

remove leading and trailing spaces2

You will see that all of leading and trailing spaces are removed from the text string in range A1:A4.

Or try to use the following formula:

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

remove leading and trailing spaces3

The CHAR(160) formula returns a non-breaking space character.

The SUBSTITUTE function will replace all non-breaking space with new space character.

The CLEAN function will remove all non-printing characters, such as line breaks.

Method 2: Remove Leading and Trailing spaces with Excel VBA macro

If you want to quickly remove leading and trailing spaces, you can write an  Excel VBA macro code, just do it following:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove leading and trailing spaces112

Sub RemoveLeadingAndTrailingSpaces()
    Set W = Application.Selection
    Set W = Application.InputBox("Select one range that you want to remove leading and trailing spaces:", "RemoveLeadingAndTrailingSpaces", W.Address, Type:=8)
    For Each R In W
        R.Value = VBA.Trim(R.Value)
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

remove leading and trailing spaces5

6# select the range of cells that you want to remove leading and trailing spaces, then click OK button.

remove leading and trailing spaces111

remove leading and trailing spaces6

 


Related Formulas

  • count specific words in a cell or a range
    If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…
  • Extract word that containing a specific character
    If you want to extract word that contains a hash character in a text string in Cell B1, you can use a combination of the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the FIND function to create an excel formula.…
  • Count the number of words in a cell
    If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..
  • Remove Spaces from a String
    how to remove extra spaces between words or text string from the specified range of cells in excel. How to delete all spaces from excel cells. …

Related Functions

  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel CHAR function
    The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel CLEAN function
    The Excel CLEAN function removes all non-printable characters from a text string.The CLEAN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the CLEAN function is as below:= CLEAN  (text)...

 

Copy a Cell to Clipboard with Single Click

This post will teach you how to use the Excel VBA macro code to copy a Cell to clipboard with only single click in Excel. How do I automatically copy the content of cell to Clipboard while clicking on the Cell or just single click on the cell in Microsoft Excel 2016, 2013 and older versions.

Normally, to copy a cell to the clipboard, you need to select a Cell firstly, press Ctrl + C shortcut to copy the content into clipboard. Whether there is any other ways to omit one or two steps to copy a cell to clipboard. Of course yes, you can try to write an Excel Macro code to copy cell content into clipboard while you click on that cell.

Copy a Cell to Clipboard with Single Click with VBA Macro

Let’s see the below detailed steps:

1# open your worksheet contain cells that you want to copy them to clipboard.

2# right click on the sheet tab, then select View Code menu from the pop-up menu list.

copy a cell to clipboard1

3# then the “Visual Basic Editor” window will appear

4# paste the below VBA code into the code window. Then clicking “Save” button.

copy a cell to clipboard2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
        Target.Copy
    End If
End Sub

5# try to click cell A1 to A4, you will see that all Cell that you clicked are copied to clipboard automatically.

copy a cell to clipboard3

If you want to use this VBA macro code, just need to update the Range as you need.

 

 

 

How to Convert Positive Numbers to Negative

This tutorial will teach you how to change positive numbers to negative with Paste special multiply operation in excel. How to convert positive number to negative or how to reverse the number signs with VBA code in Excel.

Convert Positive Numbers to Negative with Paste Special Operation

To change positive numbers to negative in excel, you can do it with copy and paste, you need to copy a cell that contains a negative number -1, then select the cells that contain the positive numbers, and use the multiply operation on the Pasted Special menu. Then it will perform the multiplication on the selected cell. So all positive number will be change to negative.

You can do it as following steps:

1# type one negative number –1 in one cell

convert positive number to negative1

2# select that cell that contains -1 and then copy it.

3# select the range of cells that contain positive numbers, then right-click on it, select Paste Special from the pop-up menu list.

convert positive number to negative2

4# the Paste Special window will appear.

5# select Multiply radio button from the Operation section.

convert positive number to negative3

6# Click OK button, you will see that all of the positive numbers should be change to negative.

convert positive number to negative4

If the selected range of cells contains negative numbers, then its values also will be multiplied by -1, so its signs also will be reversed as negative.

You can also write an Excel formula to multiply one negative number -1 to achieve the same result. the generic formula is like this:

=B1* -1

convert positive number to negative5

Convert Positive Numbers to Negative with VBA Code

You can also write a new excel macro to convert positive numbers to negative numbers in Excel VBA, just do the following:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

change positive to negative 112

Sub ChangePositiveToNegative()
    Dim myCell As Range 
    Set W = Application.Selection
    Set W = Application.InputBox("Select one range that you want to change positive to negative:", "ChangePositiveToNegative", W.Address, Type:=8)
    Set W = W.SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each myCell In W
        xValue = myCell.Value
        If xValue > 0 Then
            myCell.Value = xValue * -1
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

convert positive number to negative7

change positive to negative 111

convert positive number to negative7

 

How to Insert Multiple Checkboxes

This post will teach you how to quickly insert or add multiple checkboxes in excel. How to add one checkboxes in one cell in Excel. How to add multiple checkboxes with Fill Handle or with VBA Macro in Excel.

Add a checkbox in one cell

If you want to add one checkbox in a cell, you can do it as following steps:

1# go to Developer Tab, then click Insert command under Controls group, select Check Box.

insert multple checkboxes1

2# click one cell that you want to insert the checkbox. And it will appear in that cell.

insert multple checkboxes2

3# you can move the cursor to drag the checkbox to the desired position.

 

Insert multiple checkboxes with Fill Handle

If you want to quickly add multiple checkboxes in Excel, you can use the Fill handle to create multiple checkboxes based on one checkbox that has been created. Refer to the following steps:

1# insert one checkbox as described above

2# move the checkbox into a cell, then select that cell, Drag the Auto Fill Handle over the range of cells that you want to fill.

insert multple checkboxes3

3# you will see that multiple checkboxes have been added in each cell.

insert multple checkboxes4

Insert multiple checkboxes with VBA code

You can also write a new excel macro to quickly add multiple checkboxes in Excel VBA, just do the following:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

add multiple checkboxes 112

Sub addMultipleCheckBoxes()

  On Error Resume Next
    Set wr = Application.Selection
    Set wr = Application.InputBox("Select one Range that you want to add checkbox in:", "addMultipleCheckBoxes", wr.Address, Type:=8)
    Set wsheet = Application.ActiveSheet
    
    i = 1
    For Each R In wr
        With wsheet.CheckBoxes.Add(R.Left, R.Top, R.Width, R.Height)
            With Selection
               .Characters.Text = R.Value

               .Caption = "Check Box" & i
               i = i + 1
           End With
        End With
    Next
    wr.ClearContents
    wr.Select
End Sub

 

5# back to the current worksheet, then run the above excel macro.

insert multple checkboxes6

add multiple checkboxes 111

insert multple checkboxes6

Change the checkbox name and caption name

When you create a new checkbox, the default caption name is like as CheckBox x. so if you want to change the caption name, you just need to right click the checkbox, select the Edit Text from the drop-down menu, and then type the name that you want to set.

insert multple checkboxes9

insert multple checkboxes9

If you want to change the checkbox name, you just need to select the checkbox, then type the desired name in the Name box, it will change immediately.

insert multple checkboxes11

 

How to Delete Blank Rows in Excel 2016/2010

This post will guide you how to delete blank or empty rows or rows that contain blank cells in excel. If you want to remove blank rows that contain blank cells, you can use the Go To Special command. But if you just want to remove rows that are entirely blank, you should use the Filter blanks cell way.

Delete rows that contain blank cells

You can use the Go To Special command to remove all rows that contain blank cells, just do the following:

1# on the HOME tab, click Find & Select command under Editing group

2# click Go To Special

delete blank rows1

3# Select Blanks radio button and then click OK button.

delete blank rows2

4# you will see that all rows that contain blank cells are selected.

delete blank rows3

5# go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.

delete blank rows4

delete blank rows5

Delete Blank Rows

If you want to remove blank rows in excel, and you need to find the entire blank rows firstly, so you can use the Filter function to filter all blank rows, just do this following:

1# select the range of cells that you want to remove blank rows

delete blank rows6

2# on the DATA tab, click Filter command under Sort& Filter group.

delete blank rows7

3# click Filter arrow button and uncheck the Select All box, just check only Blanks, then all blank rows in that column will be filtered.

delete blank rows8

delete blank rows9

4# you need to continue to filter other columns for blank cells until all cells in the selected range are blank.

delete blank rows10

5# select all the filtered rows, go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.

delete blank rows11

Or just right-click on the selected cell and then click Delete Row from the drop-down menu list.

delete blank rows12

delete blank rows13

6# on the DATA tab, click Clear command under Sort & Filter group.

delete blank rows14

7# you will see that all blank rows are removed.

delete blank rows15

Delete blank rows in Excel VBA

You can also write an excel Macro to delete blank rows in excel, just refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove blank rows111

Sub RemoveBlankRows()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to remove blank rows", "RemoveBlankRows", myRange.Address, Type:=8)
    xRows = myRange.Rows.Count
    For i = xRows To 1 Step -1
        If Application.WorksheetFunction.CountA(myRange.Rows(i)) = 0 Then
            myRange.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

delete blank rows17

delete blank rows18

 

How to Reverse First Name and Last Name

This post explains that how to flip or reverse the first name and last name in one column. How to reverse the first name and last name in each cell in one column in excel. How to flip the first name and last name with excel formula or excel VBA macro.

If you need to reverse first name and last name in a column and then put the result into another cell in another column.  You need to get or extract the first name or last name firstly and then combine those names into the same cell. You can write an excel formula or write a new excel VBA code to achieve it.

Reverse First Name and Last Name with excel formula

To reverse first name and last name, you can create a formula based on the MID function, the FIND function and the Len function as follows:

=MID(B2&" " &B2, FIND(" ",B2)+1, LEN(B2))

Let’s see how this formula works:

=B2&” ” &B2

reverse first name and last name1

This formula will use the concatenate operator to join text string in B2 with text string in B2 again to generate a new text string. Then we just need to extract the second and third words from the new text string.

 

= FIND(” “,B2)+1

reverse first name and last name2

This formula will return the position of the first space character in text string of Cell B2.  Then it will pass into the MID function as its start_num argument.

 

 = LEN(B2)

It will return the length of the text string in Cell B2, then it will pass into the MID function as its num_chars argument.

 

=MID(B2&” ” &B2, FIND(” “,B2)+1, LEN(B2))

reverse first name and last name3

Reverse First Name and Last Name with excel VBA macro

You can also write an excel macro to reverse the first name and last name, you can refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

reverse name111

Sub ReverseName()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", myRange.Address, Type:=8)
    myDelemiter = Application.InputBox("Please type a delimiter character:", "ReverseName", " ", Type:=2)
    For Each myCell In myRange
        xValue = myCell.Value
        NameList = VBA.Split(xValue, myDelemiter)
        If UBound(NameList) = 1 Then
            myCell.Value = NameList(1) + myDelemiter + NameList(0)
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

reverse first name and last name8

6# select the range of cells that contains the first name and last name, then click “OK” button.

reverse name112

7# enter one delemiter character, such as: sapce character in the text box. Then click OK button.

reverse name113

8# you will see that the first name and last name have been reversed in the selected range of cells.

reverse first name and last name6


Related Formulas

  • Get Last Name From Full Name
    To extract the last name within a Full name in excel. You need to use a combination of the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function to create a complex formula in excel..…
  • Get First Name From Full Name
    If you want to get the first name from a full name list in Column B, you can use the FIND function within LEFT function in excel. The generic formula is as follows:=LEFT(B1,FIND(” “,B1)-1).…
  • Split full name to first and last name
    You can create another formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function to extract the last name……
  • Extract text before first comma or space
    If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….
  • Extract nth word from text string
    If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…

Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…

 

Unhide All Worksheets

This post explains that how to unhide all hidden worksheets or sheets in Microsoft Excel. This tutorial will let you learn that how to unhide all worksheets in a workbook with an Excel Macro.

Unhide All Sheets with Macro

It’s very easy to hide one worksheet or unhide one worksheet in excel, but if you want to unhide or hide multiple or all worksheets in a given workbook, it should be a tedious exercise.

You may be think that if there is a way to unhide all sheets at the same time. Of course yes, the best way to unhide all the worksheets in current workbook is to use an excel macro in VBA. The below steps will discuss that how to write an Excel macro to unhide multiple sheets.

1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

unhide all worksheet1

Sub UnhideAllWorksheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws 
End Sub

5# back to the current worksheet, then run the above excel macro.

unhide all worksheet2

unhide all worksheet3

You will see that all hidden sheets have been unhidden in your workbook.

Unhide worksheets that contain specific sheet name

If you just want to unhide the specific worksheets in your current workbbok, such as, just want to show the worksheet that sheet name contain the work “excel”, you can also use a macro to quickly unhide worksheets that contain a specific name in the worksheet name.

unhide specific worksheet1

Sub UnhideSpecificWorksheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "excel") > 0 Then
            ws.Visible = xlSheetVisible
        End If 
    Next ws
End Sub

unhide specific worksheet2

So you just need to update the specific text string in IF statement as you need. Then run this macro in your workbook, it will unhide all specific worksheets.

Unhide all worksheets by manually

If you just want to unhide few worksheets, then you can also unhide multiple worksheets one by one. Just follow the below steps:

1# select one worksheet name in sheet tab, right click on it, and then choose Unhide… menu from the popup menu list.

unhide all worksheet4

2# the Unhide dialog box will appear, it will list all hidden worksheet. You can select one that you want to unhide.

unhide all worksheet5

Note: it only allows you to select one worksheet at a time.

3# click OK button.

unhide all worksheet6

 

How to Check If the First Letter is capitalized

This post explains that how to check if the first letter in a cell is capitalized or not in excel. How to write an excel Macro to check if the first letter in a specified cell is capitalized or not. How to test if the first letter in a table cell is capitalized with VBA macro in excel. How to capitalize the first letter in a cell with Excel formula.

Check if the first letter is or not capitalized

To check if the first letter in a text string is or not capitalized, you can write an Excel macro to achieve it. So you can follow the below steps to check the first letter:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

check if first letter capitalized1

Sub TestFirstLetterUpper()
    Dim xRg As Range
    Dim xAsc As Integer
    Set xRg = Range("B1")
    xAsc = Asc(Mid(xRg.Value, 1, 1))
    If xAsc > 64 And xAsc < 91 Then
        MsgBox "The first letter in Cell B1 is capitalized."
    Else
        MsgBox " The first letter in Cell B1 is not capitalized "
    End If
End Sub

This VBA code will check if the first letter in Cell B1 is capitalized or not. So you just need to change it to other cells or range as you need.

Or you can use another VBA Macro to achieve the same result:

Sub TestFirstLetterUpper2()
    var1 = "B1"
    If UCase(Left(var1, 1)) = Left(var1, 1) Then
        MsgBox "First letter capitalized"
    Else
        MsgBox "First letter not capitalized"
    End If
End Sub

5# back to the current worksheet, then run the above excel macro.

check if first letter capitalized2

check if first letter capitalized2

Capitalize the first letter in a Cell

Normally, it is very easy to capitalize all letters in a cell in excel, you just need to use the PROPER function to convert it.

=PROPER(B1)

capitalized first letter1

If you want to capitalize only the first letter in a cell, you can create an excel formula based on the REPLACE function, the UPPER function and the LEFT function.

Assuming that you’d like to capitalize the first letter in a first word in Cell B1, then you can write down the following formula:

=REPLACE(B1,1,1, UPPER(LEFT(B1,1)))

check if first letter capitalized4

You can also use another excel formula to achieve the same result as follows:

=CONCATENATE(UPPER(LEFT(B1,1)), RIGHT(B1,LEN(B1)-1))

check if first letter capitalized5


Related Functions

  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
  • Excel Replace function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day) …
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel UPPER function
    The Excel UPPER function converts all characters in text string to uppercase.The UPPER function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the UPPER function is as below:= UPPER (text)…
  • Excel Proper Function
    The Excel PROPER function capitalizes the first character in each word of a text string and set other characters to lowercase. The syntax of the PROPER function is as below:= PROPER  (text)…
  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…

How to Unhide/Hide Rows or Columns with a Cell Double Click

This post explains that how to unhide or hide the specified rows or columns while you double clicking one cell in Excel. How to write an excel VBA code to unhide or hide rows or columns when the user double clicks a certain cell. How to hide or unhide rows or columns when you double-click on any cell of one range with VBA code.

Normally, you can select rows that you want to hide or unhide via clicking row number firstly, then right-click on row, then choose “Hide” or “Unhide” menu from pop-up menu list to hide or unhide rows or columns.

Hide/Unhide Rows with a Cell Double Click

If you want to hide or unhide rows quickly while you double click any one cell in one range, the best way is to write an Excel VBA code to achieve this requirement. Let’s see the below steps:

1# open your worksheet file, then right-click on the sheet tab that you want to hide or unhide rows.

hide unhide rows with doubleclick1

2# choose View code from the popup menu.  Then the “Visual Basic Editor” window will appear.

3# then copy the below VBA private macro into code window.

hide unhide rows with doubleclick2

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim hideRows As Range
    If (Not Intersect(Target, Range("A1:A3")) Is Nothing) And (Target.Count = 1) Then
        Set hideRows = Range("2:3")
        hideRows.EntireRow.Hidden = Not hideRows.EntireRow.Hidden
    End If
End Sub

4# when you double click any one cell in range A1:A3, then the row2 and row 3 will be hidden.

hide unhide rows with doubleclick3

And when you double click any cell in the range A1:A3, then the hidden rows will be displayed.

hide unhide rows with doubleclick4

Hide/Unhide Columns with a Cell Double Click

If you want to hide or unhide columns quickly while you double click any one cell in one range, you can also write an Excel private VBA macro to achieve it. Let’s see the below steps:

1# open your worksheet file, then right-click on the sheet tab that you want to hide or unhide rows.

hide unhide rows with doubleclick1

2# choose View code from the popup menu.  Then the “Visual Basic Editor” window will appear.

3# then copy the below VBA private macro into code window.

hide unhide rows with doubleclick5

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim hideColumns As Range
    If (Not Intersect(Target, Range("A1:A4")) Is Nothing) And (Target.Count = 1) Then
        Set hideColumns = Range("C:D")
        hideColumns.EntireColumn.Hidden = Not hideColumns.EntireColumn.Hidden
    End If
End Sub

4# when you double click any one cell in range A1:A3, then the Column D and Column D will be hidden.

hide unhide rows with doubleclick6

And when you double click any cell in the range A1:A3, then the hidden Columns will be displayed.

hide unhide rows with doubleclick7

Hide/Unhide Columns with a Cell Right Click

If you want to hide or unhide rows or columns when use right click on any one cell in a range, you just need to change the worksheet event from beforeDoubleClick to BeforeRightClick in the VBA code window.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim hideColumns As Range
    If (Not Intersect(Target, Range("A1:A3")) Is Nothing) And (Target.Count = 1) Then
    Set hideColumns = Range("C:D")
    ideColumns.EntireColumn.Hidden = Not hideColumns.EntireColumn.Hidden
    End If
End Sub

When you execute this private macro, you will see that when you right click on any one cell in range A1:A3, Column C and D will be hidden automatically.

 

How to Convert Absolute Reference to Relative Reference

This post will guide you how to convert the cell reference from absolute reference to relative reference in excel. How to change the absolute reference of cells to relative using VBA macros in excel. How to change the reference to absolute reference to keep excel formulas while sorting cells that contain formulas.

Convert reference from absolute to relative

The first way is that you can press F4 key to change cell reference from absolute to relative. Just refer to the following steps:

1# select the cell that contains the reference you want to change.

convert absolute to relative1

2# select the reference of references that you want to convert

convert absolute to relative2

3# press F4 key three times.

convert absolute to relative3

Note: if you type a relative reference into formula box, then press F4, the reference will change to absolute. When you press F4 again, the reference changes to mixed type with the row fixed. So you need to press F4 key three times in the above steps.

You will see that the cell reference has been changed to relative reference.

Convert reference from relative to absolute

The cell references are set up as relative by default in Excel. It means that when you copy the cell contains formula to another cell, the cell reference will be changed automatically. If the original cell use the absolute reference, the formula can be kept without changed.

The simplest method is that you just need to press F4 key on the selected relative reference in formula box.

convert absolute to relative4

Convert reference from absolute to relative using VBA macro

You can also write an excel macro to quickly change the type of cell reference from absolute to relative. Refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

convert reference type111

Sub ConverReferenceType()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to covert reference type:", "ConvertReferenceType", myRange.Address, Type:=8)
    Set myRange = myRange.SpecialCells(xlCellTypeFormulas)
     myIndex = Application.InputBox("Select a reference type from below list:" & Chr(13) & Chr(13) _
    & "Absolute = 1" & Chr(13) _
    & "Row absolute = 2" & Chr(13) _
    & "Column absolute = 3" & Chr(13) _
    & "Relative = 4", "ConvertReferenceType", 1, Type:=1)
    For Each R In myRange
       R.Formula = Application.ConvertFormula(R.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, myIndex)
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

convert absolute to relative6

convert absolute to relative6

convert absolute to relative6

 

How to clear all print area in active excel workbook

In the previous post, we talked that how to set same area to multiple worksheets in active workbook.  And this post explains that how to quickly clear all print area in multiple sheets at once in excel.

Clear All Print Areas for all worksheets

You can write an excel macro to quickly clear all print area in an active workbook. You just need to refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub ClearAllPrintArea()
    Dim s As Worksheet
    For Each s In Worksheets
        s.PageSetup.PrintArea = ""
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

Clear Print Area in Current Worksheet

If you want to clear a print area or multiple print areas in the current worksheet, then clearing a print area will remove all the print areas in your worksheet.

1# click any cells on the worksheet that you want to clear the print area.

2# click “Page Layout”-> “Print Area”-> “Clear Print Area”.

clear print area1


 Related Posts

  • How to Set Same Print Area to Multiple worksheets
    Assuming that you have a workbook with 20 worksheets and you want to set the same print area in each worksheet. This post will teach you how to write a new excel macro to set print are in multiple worksheets.…

How to Set Same Print Area to Multiple worksheets

This post will guide you how to set the same print area to multiple worksheets at the same time in excel. How to quickly set same print area in multiple worksheet using Excel VBA macro.

Set Print Area in a worksheet

If you want to set the print area in a worksheet, you can refer to the following steps:

1# select the range of cells that you want to set as the print area in your worksheet.

2# Go to PAGE LAYOUT Tab, click Print Area command under Page Setup group, then click Set Print Area.

set print area1

3# the selected cells should be added into the print area. You can click print preview to check it.

4# if you want to add cells to the existing print area, you just need to select the cells that you want to add, then click “Page Layout”->  “Print Area”-> “Add Print Area”.

set print area2

5# if you want to remove exist print area, you can click Clear Print Area from the drop-down list of Print Area.

Set Same Print Area to Multiple worksheets using excel macro

Assuming that you have a workbook with 20 worksheets and you want to set the same print area in each worksheet. In general, we just can set the print area for only one worksheet, how to set print area for multiple worksheets in excel? This post will teach you how to write a new excel macro to set print are in multiple worksheets.

If you want to set same print area for all the worksheets in an active workbook, you can use the following excel macro.

Sub SetPrintAreaAllWorksheets()
    Dim w As Worksheet
    Dim R As Range
    Set R = Application.Selection
    For Each W In Application.ActiveWorkbook.Worksheets
        W.PageSetup.PrintArea = R.Address
    Next
End Sub

 

If you just want to set same print area for the selected worksheets in an active workbook, you can use the following excel macro.

Sub SetPrintAreaSelectedWS()
    Dim w As Worksheet
    Dim R As Range
    Set R = Application.Selection
    For Each W In Application.ActiveWorkbook.SelectedSheets
        W.PageSetup.PrintArea = R.Address
    Next
End Sub

 Related Posts