Macro to filter out text contentsClosed

Question

i have Column E filled with numbers, formulas and text. I would like to write a macro to filter out the cells with text contents. The text contents do not have any fixed format. 

Pls advice.

TrowaD · Answer

Hi Mayek,

The following code will hide rows if the value in column E is neither number nor formula:
Sub FilterOutText()
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    If IsNumeric(cell) = False And cell.HasFormula = False Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub
Best regards,
Trowa

mayek25 · Answer

Hi Trowa,

Many thanks for your help. Appreciate it a lot. 

Could you pls advice how do I add in Date into my filter as well? i.e. 3rd filter

Thanks again for all your help.

Regards

TrowaD · Answer

Hi Mayek,

You can either check if the left most number/letter is a number:
If IsNumeric(cell) = False And cell.HasFormula = False And IsNumeric(Left(cell, 1)) = False Then
Or check the format of the cell (make sure you format the dates the same as in the code):
If IsNumeric(cell) = False And cell.HasFormula = False And cell.NumberFormat <> "m/d/yyyy" Then
Choose the one you like the most.

Best regards,
Trowa

Macro to filter out text contents

3 responses

Similar discussions

Subscribe To Our Newsletter!