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 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 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.

3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
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
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0