How to Filter a column by text which has blank space in start

[Sachin2k6]

Hi friends,
i have a data column in excel which has text containing blank space in start in some cells. how can i filter these cells which has text containing blank space in start.

 

[Sachin2k6]

Would a VBA solution work for you?

If yes, then try this

I am assuming that the data is in Col "A" of "Sheet1". Change the code below accordingly.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim FilteredRange As Range
  
    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
  
    With ws
        .AutoFilterMode = False
      
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
      
        '~~> Identify the rows that needs to be hidden
        For i = 2 To lRow
            If Left(.Range("A" & i).Value2, 1) <> " " Then
                If FilteredRange Is Nothing Then
                    Set FilteredRange = .Rows(i)
                Else
                    Set FilteredRange = Union(.Rows(i), FilteredRange)
                End If
            End If
        Next i
      
        '~~> Hide rows if applicable
        If Not FilteredRange Is Nothing Then FilteredRange.EntireRow.Hidden = True
    End With
End Sub

View attachment 56024

Thank you so much.

 

[Peter_SSs]

Thank you so much.

Your question was to filter the columns. The chosen solution in fact does not filter the column as such but hides the irrelevant rows. It is the same visual result of course but you don't have the AutoFilter option at the top to then unhide the hidden rows. Did you try the other suggestions too?

 

[Sachin2k6]

My purpose has solved with your code. Thanks.

Your question was to filter the columns. The chosen solution in fact does not filter the column as such but hides the irrelevant rows. It is the same visual result of course but you don't have the AutoFilter option at the top to then unhide the hidden rows. Did you try the other suggestions too?