How to Show Zeros (0) in Empty Cells in Excel | How to Display Zeros (0) in Blank Cells in Excel

How to Show Zeros (0) in Empty Cells in Excel | How to Display Zeros (0) in Blank Cells in Excel

To Show Zeros (0) in Empty Cells, 

1. Open Excel Sheet and select the range of cells where you want to show Zeros (0) which is shown in below Figure 1.

How to Show Zeros (0) in Empty Cells in Excel | How to Display Zeros (0) in Blank Cells in Excel

2. After Selecting the Cells, Press Ctrl + F (find select). In find window click on replace and click in replace with and enter zero (0) and press replace all.(Shown in Fig2)

How to Show Zeros (0) in Empty Cells in Excel | How to Display Zeros (0) in Blank Cells in Excel

3. Now All the Blank Cells in the selected range are filled with Zeros (0). (Shown in Fig 3)

How to Show Zeros (0) in Empty Cells in Excel | How to Display Zeros (0) in Blank Cells in Excel 

Macro to hide rows with number zeros in Excel | VBA Code to hide rows with number zeros in Excel

To hide rows with zeros (0) in excel do the following steps using VBA Code.

1. Open active excel sheet 

2. Press Alt + F11 and Visual Basic Application window will open.

3. Go to Insert Menu and select new Module.

4. In new Module insert the below VBA Code and save it.

5. Before do the above process, excel sheet must save as Macro-Enabled.

6. To do Macro Enabled, open Excel Sheet and Click on the Office Button which is at left top corner on excel sheet.

7.  Select Save as and again select Macro Enabled Worksheet and then save it.

VBA Code to Hide Rows With Zeros

Sub HideRows()

Dim cell As Range

For Each cell In Range("J19:J33")

If Not isEmpty(cell) Then

If cell.Value = 0 Then 

cell.EntireRow.Hidden = True

End If

End If

Next

End Sub

Now easily hide rows with Zeros.

How to delete hidden rows after filter in excel using VBA Code | How to delete hidden rows after filter in excel using VBA Macro

How to delete hidden rows after filter in excel using VBA Code | How to delete hidden rows after filter in excel using VBA Macro

VBA Code

Open Excel Sheet and Press Alt F11.

Go to insert menu and select new module.

in the new module paste the below code and save.

Sub deletehidden()

For lp = 256 To 1 Step -1

If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else

Next

For lp = 65536 To 1 Step -1

If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else

Next

End Sub

After saving the above code go the active excel sheet and select developer tab.

Click on Macros and select saved macro and click on run.

Now all the hidden rows in the filtered excel sheet will delete.

Click here to Know more Macros

How to Create a Shortcut Key to Paste Special Values in Excel | VBA Code to Paste Special Values in Excel | Macro to Paste Special Values in Excel

How to Create a Shortcut Key to Paste Special Values in Excel | VBA Code to Paste Special Values in Excel | Macro to Paste Special Values in Excel

Sub PasteVal()

    Selection.PasteSpecial Paste:=xlValues

End Sub

How to Paste Macro in Excel Module

Open Excel Sheet and Press Alt F11 Keys.

A New Window will Open and go to Insert Menu.

In Insert menu click on Module.

After inserting Module, a new window will open and copy and paste the above code in that window.

save the code after pasting in  the module.

Before this process, make excel sheet as Macro-Enabled Work Sheet.