VBA Excel How to hide blank rows

LEARN MORE WITH THESE BOOKS

Three easy ways to hide blank rows in Excel

One recurring situations in the use of EXCEL is to hide blank lines that, intentionally or not, we will leave behind as we build a spreadsheet. Later, we found that those lines would be better hidden.
One option is to quickly and consistently use a small VBA macro that, through one or more criteria, allows one-click to select the lines you want to hide.
Here I leave three possibilities:

1.
Hide all blank lines from the line 2 to the 65000

Sub OcultarLinha()
Dim Linha, LinhaFinal As Integer
LinhaFinal = Range("A65000").End(xlUp).Row
For Linha = 2 To LinhaFinal
If Range("A" & Linha).Value = 0 Then
Range("A" & Linha).EntireRow.Hidden = True
Else
End If
Next Linha
End Sub

2.
Hide all rows in the range between line 4 and line 500, that in column "G" cells contain blank or zero (processing faster than the alternative 1)

Sub OcultarLinha()

Dim i As Integer Application.ScreenUpdating = False
With Sheets("sumario")
.Cells.EntireRow.Hidden = False For i = 4 To 500
Select Case .Range("g" & i).Value
Case 0
.Rows(i & ":" & i).EntireRow.Hidden = True
End Select
Next i
End With Application.ScreenUpdating = True
End Sub

3.
Enabling AutoFilter via VBA. The intention is to hide the lines in column 7 ("G") containing the caracter "s" (that faster processing of the above)

Sub OcultarLinha()
Sheets("Folha1").Select
Range("A2:G500").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="s"End Sub

Comments 2 comments

Johne701 2 years ago

Excellent post. I was checking continuously this blog and I'm impressed! Extremely useful information specially the last part fddkedacdbaf


Ron Kanij 2 years ago

I definitely recommend not to use .Select

The code below can replace code 3.

Sub OcultarLinha()

Sheets("Folha1").Range("A2:G500").AutoFilter 7, "s"

End Sub

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working