hide blank rows automatically



I have a table with data in column A over than 500 lines that is auto-populated from another sheet, however some rows are blank with a IF function =IF(Sheet2!A1="","",Sheet2!A1) and those are the rows that I want to hide and unhide automatically, I don't like to use a macro, I want it should go automatically with VBA, I search the web but whatever I find was taking to long, I didn't find something to work good for me,

Or what's can be good for me is: when I sort the table should all blank cells goes to the bottom,

usually when you sort a column all empty cells goes to bottom by default but cells with formulas goes to the top even if it's empty, i use this VBA code to sort when I select A1, if somebody can add code to this that all blank cells should go to the bottom

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim r As Long

    If Not Intersect(Target, Range("A1")) Is Nothing Then

    r = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A2:A" & r).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

    End If

End Sub




So I don't see an example attached to best assist you, but I believe the simplest way to accomplish this is to Create a Filter for your data.  Once filter is created ou can UNSELECT blanks to virtually hide your blank rows in Column A. Alternatively, if you sort and change the order to highest to lowest (the opposite of what I believe you were trying?) you will then filter the BLANKS to the bottom.


Answer the Question

You must create an account to use the forum. Create an Account or Login