=LEFT(D2,1)=" "
Thanks for helping but can u tell any other way which not required adding helper column?Add a helper column and use the below formula. I have used D and E as an example. Edit the formula accordingly.
Excel Formula:=LEFT(D2,1)=" "
And then filter on TRUE in the helper column
View attachment 56019
Thanks for helping but can u tell any other way which not required adding helper column?
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
Sub AnotherSample()
Dim cell As Range, DataRange As Range, TargetRange As Range
'change this to refer to your actual worksheet
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'change this to refer to range that includes your data
Set DataRange = ws.UsedRange.Offset(RowOffset:=1)
On Error Resume Next
With ws
.ShowAllData
.Cells.EntireRow.Hidden = False
End With
On Error GoTo 0
For Each cell In DataRange
If cell.Value2 Like "[ ]*" Then
If TargetRange Is Nothing Then
Set TargetRange = cell.EntireRow
Else
Set TargetRange = Union(TargetRange, cell.EntireRow)
End If
End If
Next
If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = True
End Sub
Hello everyone,
I don't know what my first answer was about, maybe because I was just waking up. Anyway, here's another solution similar to Rout's.
VBA Code:Sub AnotherSample() Dim cell As Range, DataRange As Range, TargetRange As Range 'change this to refer to your actual worksheet Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'change this to refer to range that includes your data Set DataRange = ws.UsedRange.Offset(RowOffset:=1) On Error Resume Next With ws .ShowAllData .Cells.EntireRow.Hidden = False End With On Error GoTo 0 For Each cell In DataRange If cell.Value2 Like "[ ]*" Then If TargetRange Is Nothing Then Set TargetRange = cell.EntireRow Else Set TargetRange = Union(TargetRange, cell.EntireRow) End If End If Next If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = True End Sub
View attachment 56034View attachment 56035
Sub AnotherSample()
Dim cell As Range, DataRange As Range, TargetRange As Range
'change this to refer to your actual worksheet
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'change this to refer to range that includes your data
Set DataRange = ws.UsedRange.Offset(RowOffset:=1)
On Error Resume Next
With ws
.ShowAllData
.Cells.EntireRow.Hidden = False
End With
On Error GoTo 0
For Each cell In DataRange
If cell.Value2 Like "[ ]*" Then
If TargetRange Is Nothing Then
Set TargetRange = cell.EntireRow
Else
Set TargetRange = Union(TargetRange, cell.EntireRow)
End If
End If
Next
DataRange.EntireRow.Hidden = True
If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = False
End Sub
What would you enter into the "contains" box?i'm not sure of 2007 already had textfilters in its filterfunction otherwise you can use those with "contains"
Sub Filter_Blank_First()
ActiveSheet.FilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("if(left(#,1)="" "",""%%%"","""")&#", "#", .Address))
.AutoFilter Field:=1, Criteria1:="%%% *"
.Replace What:="%%%", Replacement:="", LookAt:=xlPart
End With
End Sub
Sachin2k6.xlsm | |||
---|---|---|---|
A | |||
1 | Data | ||
2 | Data 1 | ||
3 | Data 2 | ||
4 | Data 3 | ||
5 | Data 4 | ||
6 | Data 5 | ||
7 | Data 6 | ||
8 | Data 7 | ||
9 | |||
10 | Data 9 | ||
11 | Data 10 | ||
12 | Data 11 | ||
13 | Data 12 | ||
14 | |||
Sheet1 |
Sachin2k6.xlsm | |||
---|---|---|---|
A | |||
1 | Data | ||
4 | Data 3 | ||
6 | Data 5 | ||
7 | Data 6 | ||
11 | Data 10 | ||
14 | |||
Sheet1 |