Tag: go to special

This post will guide you how to delete all comments in your current worksheet in Excel. How do I delete all comments from a worksheet with VBA code in Excel.

• Delete All Comments with Delete Command
• Delete All Comments with Go To Special Feature
• Delete All Comments with VBA

Delete All Comments with Delete Command

If you have a worksheet with lots of cell comments that you want to delete. And is there a quick way to delete all comments in your current worksheet without going from cell to cell and deleting them individually. You can use the Delete comments command to delete all comments in your selected range. Just do the following steps:

#1 If you want to delete all comments in current worksheet, press Ctrl + A to select all cells.

#2 go to REVIEW tab, click Delete command under Comments group. And then all comments would be removed in your current worksheet.

Delete All Comments with Go To Special Feature

You can also use the Go To Special Feature to select all cells that contain comments. And then delete all comments. Here are the steps:

#1 go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the drop down menu list. And the Go To Special dialog will open.

#2 select Comments radio button in the Go To Special dialog. And click Ok button. Then all the cells that contain comments should be selected.

#3 right click on it, and click Delete Comment from the popup menu list. And all the comments would be deleted in the selected cells in your worksheet.

Delete All Comments with VBA

You can also use an Excel VBA macro to achieve the same result of deleting all comments in the current worksheet. Just do the following steps:

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

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

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

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

Sub DeleteAllCommentsFromWorksheet()
   Cells.ClearComments
End Sub

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

This post will guide you how to unmerge cells and fill with duplicate values in Excel 2013/2016. How do I unmerge multiple cells and copy the content in each previously merged cell in Excel. How to unmerge cells and fill down duplicate values with VBA Macro in Excel.

• Unmerge Cells and Fill with Duplicate Values
• Unmerge Cells and Fill with Duplicate Values using VBA Macro
• Video: Unmerge Cells and Fill with Duplicate Values

Unmerge Cells and Fill with Duplicate Values

Assuming that you have a list of data in range A1:C4, and some cells are merged in your table. And you want to unmerge cells in this range and fill each cell with the original value. How to achieve it.  You need to unmerge cells in selected range firstly, then select all blank cells with Go To Special feature, next you need to use a formula to fill down value in each blank cell. Just do the following steps:

#1 select the range of cells which contain merged cells.

#2 go to HOME tab, click Merge & Center command under Alignment group, and select Unmerge Cells from the drop down menu list.

#3 all merged cells in the selected range have been unmerged. And keep to select the range.

#4 go to HOME tab, click Find & Select command under Editing group, and select Go To Special menu from the drop down menu list. And the Go To Special dialog will open.

#5 select Blanks radio button in the Select section, and click OK button.

#6 all blank cells have been selected. And then enter =, then press Up arrow in your keyboard.

#7 press Ctrl + Enter keys on your keyboard, and all blank cells have been filled with the original merged value.

Unmerge Cells and Fill with Duplicate Values using VBA Macro

You can also use an Excel VBA Macro to unmerge multiple cells in a selected range and then fill with duplicate values in each cell. Just do the following steps:

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

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

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

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

Sub UnmergeMulCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range which contain merged Cells", "UnmergeMulCells", myRange.Address, Type:=8)

    For Each myCell In myRange
        If myCell.MergeCells Then
            With myCell.MergeArea
                .UnMerge
                .Formula = myCell.Formula
            End With
        End If
    Next
End Sub

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

#6 Select one Range which contain merged Cells.

#7 Let’s see the last result:

Video: Unmerge Cells and Fill with Duplicate Values

This post will guide you how to delete all visible rows or columns in Excel. How do I select only the visible cells in Excel 2013/2016. How to delete only visible rows without the hidden rows in Excel.

• Delete Visible Rows with Go To Special Feature
• Delete Visible Rows with VBA Macro
• Video: Delete Visible Rows

Delete Visible Rows with Go To Special Feature

Assuming that you have a list of data in range A1:C4, and you have hidden one row (Row 2) in your table, and you want to delete all visible data in your range, if you select range A1:C4, all hidden values also will be deleted. So how to only select the visible values and then delete only visible values without the hidden values in Excel. You just need to do the following steps:

#1 select the range of cells A1:C4 that you want to delete all visible values.

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special menu from the drop-down list. And the Go To Special dialog will open.

#3 select Visible cells only radio button under Select section. And click OK button.

#4 only visible cells will be selected, and if you delete those values, just press Delete key in your keyboard.

#5 all visible values are deleted. And you can show all hidden values if they are still kept.

Delete Visible Rows with VBA Macro

You can also use an Excel VBA Macro to achieve the result of deleting all visible rows in your selected range of cells. Just do the following steps:

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

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

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

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

Sub DeleteAllVisibleRows()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to delete visible rows", "DeleteAllVisibleRows", myRange.Address, Type:=8)
    myRange.SpecialCells(xlCellTypeVisible).ClearContents
End Sub

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

#6 Select one range that you want to delete all visible rows, such as:A1:C4.

#7 let’s see the result:

Video: Delete Visible Rows