ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software

VBA Excel How to hide blank rows

Updated on August 5, 2011

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:

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
End If
Next Linha
End Sub

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

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()
Selection.AutoFilter Field:=7, Criteria1:="s"End Sub


    0 of 8192 characters used
    Post Comment

    • profile image

      Ron Kanij 3 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

    • profile image

      Johne701 3 years ago

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