Macro to filter out text contents

Closed
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 16, 2013
-
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
-
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.

3 replies

Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
491
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
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 16, 2013

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
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
491
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