highlight cells automatically based on values in other cells in excel using Conditional Formatting

How to Sum Values in Excel Sheet

How to change column width and row height automatically

VBA Code to hide rows with previous dates in excel | Macro to hide rows based on dates in excel

VBA Code to hide rows with previous dates in excel | Macro to hide rows based on dates in excel

Before applying Macro, follow the steps to apply Macro on Excel.

Open Excel Sheet

Press Alt + F11 Key 

Go to the Insert Menu and Click on new Module

in New Module copy and paste the above VBA code and save.

Now Press Alt + F8  and new windows will open which is shown in below figure,

in that windows you will ask to enter a key to create shortcut for Paste Special

Enter a letter in the box. 

Sub Hide_Dates()

Dim MyRange As Range, c As Range

Set MyRange = Range("H2:H4436")

MyRange.EntireRow.Hidden = False

For Each c In MyRange

    If IsDate(c.Value) And c.Value < Date Then

        c.EntireRow.Hidden = True

    End If

Next

End Sub

Excel Tricks : VBA Code to find Unique Values in excel | Macro to highlight unique values in excel

Excel Tricks : VBA Code to find Unique Values in excel | Macro to highlight unique values in excel 

To highligh unique values 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.

Sub highlightUniqueValues()

Dim rng As Range

Set rng = Selection

rng.FormatConditions.Delete

Dim uv As UniqueValues

Set uv = rng.FormatConditions.AddUniqueValues

uv.DupeUnique = xlUnique

uv.Interior.Color = vbGreen

End Sub

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.