Macro to filter out text contents

Closed
mayek25 Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 16, 2013 - Jul 15, 2013 at 01:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 18, 2013 at 10:15 AM
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.
Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 16, 2013 at 11:43 AM
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 Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 16, 2013
Jul 16, 2013 at 10:37 PM
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 Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 18, 2013 at 10:15 AM
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