Tags: code, column, excel, hide, inanother, microsoft, msdn, realise, row, rows, software, whoel, worksheet

hide blank rows

On Microsoft » Microsoft Excel

3,887 words with 3 Comments; publish: Wed, 04 Jun 2008 00:27:00 GMT; (30662.50, « »)

i need to hide all those blank row in my whoel worksheet. found this code in

another thread but realise that this hide the row if that column is blank.

however, what i want is to only hide the row if whole row is w/o data and not

particular column w/o data. so hw can i modify this code or is that any

other code to acheive this?

Sub Tester()

On Error Resume Next

Range("A1:o20").Columns(1).SpecialCells(xlBlanks). _

EntireRow.Hidden = True

On Error GoTo 0

End Sub

All Comments

Leave a comment...

  • 3 Comments
    • Hi Violet

      Below is not elegant but I think it should work OK, just change your sheet

      name and range

      Sub Tester()

      Dim rng As Range

      Dim x As Long

      With Worksheets("Sheet4")

      Set rng = Worksheets("Sheet4").Range("b2:b20")

      For Each c In rng

      x = c.Row

      If .Range("A" & x).End(xlToRight).Column = 256 Then

      .Range("A" & x).EntireRow.Hidden = True

      End If

      Next

      End With

      End Sub

      Tony Green

      "violet" wrote:

      > i need to hide all those blank row in my whoel worksheet. found this code in

      > another thread but realise that this hide the row if that column is blank.

      > however, what i want is to only hide the row if whole row is w/o data and not

      > particular column w/o data. so hw can i modify this code or is that any

      > other code to acheive this?

      > Sub Tester()

      > On Error Resume Next

      > Range("A1:o20").Columns(1).SpecialCells(xlBlanks). _

      > EntireRow.Hidden = True

      > On Error GoTo 0

      > End Sub

      #1; Wed, 04 Jun 2008 00:28:00 GMT
    • You can do it without code by creating a column that concatenates all

      other columns, then use autofilter on that column and set the filter

      condition to non-blanks and it will hide all columns that contain no

      data at all.

      Alternatively, the following code will loop through each row in the

      selection and check its entire row on the worksheet. If there are no

      values in any cells on that row, the code sets its hidden property to

      true.

      Sub Hide()

      For i = 1 To Selection.Rows.Count

      If Application.CountA(Rows(Selection.Rows(i).Row)) = 0 Then

      Selection.Rows(i).Hidden = True

      End If

      Next i

      End Sub

      violet wrote:

      > i need to hide all those blank row in my whoel worksheet. found this code in

      > another thread but realise that this hide the row if that column is blank.

      > however, what i want is to only hide the row if whole row is w/o data and not

      > particular column w/o data. so hw can i modify this code or is that any

      > other code to acheive this?

      > Sub Tester()

      > On Error Resume Next

      > Range("A1:o20").Columns(1).SpecialCells(xlBlanks). _

      > EntireRow.Hidden = True

      > On Error GoTo 0

      > End Sub

      #2; Wed, 04 Jun 2008 00:29:00 GMT
    • Sub HideRows()

      Dim oRow As Range

      For Each oRow In Range("A1:A20").EntireRow

      oRow.Hidden = Application.CountIf(oRow, "<>") = 0

      Next oRow

      End Sub

      HTH

      Bob Phillips

      (replace somewhere in email address with gmail if mailing direct)

      "violet" <violet.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:D9F165D5-B29F-4080-8565-46CA47ADCDF1.excel.todaysummary.com.microsoft.com...

      > i need to hide all those blank row in my whoel worksheet. found this code

      in

      > another thread but realise that this hide the row if that column is blank.

      > however, what i want is to only hide the row if whole row is w/o data and

      not

      > particular column w/o data. so hw can i modify this code or is that any

      > other code to acheive this?

      > Sub Tester()

      > On Error Resume Next

      > Range("A1:o20").Columns(1).SpecialCells(xlBlanks). _

      > EntireRow.Hidden = True

      > On Error GoTo 0

      > End Sub

      #3; Wed, 04 Jun 2008 00:30:00 GMT