Filter out Blanks when work book is active

[eli_m]

Hi,

I have this VB code:

'Sort Case Cost
Private Sub Worksheet_Activate()

  With Sheets("Case Cost")
    On Error Resume Next
    ThisWorkbook.Sheets("Case Cost").Range("B1").Sort Key1:=ThisWorkbook.Sheets("Case Cost").Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

    On Error GoTo 0
    For Each cel In .Range("B1:B100")

    Next cel
  End With
  Application.ScreenUpdating = True
  
End Sub

Which works great but my problem is that I have a formulas in Column B which makes all the blanks go to the top.

Is there a way I can filter out the blanks straight after the code above when the worksheet is activated?
When I do it manually it's exactly how I want it to be displayed.

Thanks in advance!

 

[Alex Blakenburg]

What do you do when you do it manually ?
ie do you want to filter those rows or delete them ?
And if you and if you wan to filter them is Column B the first or 2 column in your data range ?

 

[Alex Blakenburg]

See if the below does what you want.
If you want to delete the rows and not just filter them out then comment out the line after hide blank rows and uncomment the lines under delete blank rows.
PS: Assuming your code is in the worksheet module of Case Cost, you don't need to reference that sheet.

Private Sub Worksheet_Activate()
'Sub test()
    Dim cel As Variant
    Application.ScreenUpdating = False
    
    On Error Resume Next
        Range("B1").Sort Key1:=Range("B1"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    On Error GoTo 0

    If ActiveSheet.FilterMode = True Then
        ActiveSheet.ShowAllData
    End If
    
    Dim rng As Range
    Dim fltrCol As Long
    Set rng = Range("B1").CurrentRegion
    fltrCol = Range("B1").Column - rng.Column + 1
    
    ' --- hide blank rows ---
    rng.AutoFilter Field:=fltrCol, Criteria1:="<>"
    
    ' --- delete blank rows ---
     'rng.AutoFilter Field:=fltrCol, Criteria1:="="
     'rng.Offset(1).Resize(rng.Rows.Count - 1).EntireRow.Delete
     'ActiveSheet.ShowAllData

    Application.ScreenUpdating = True
  
End Sub