Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default hide blank rows

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   Report Post  
Posted to microsoft.public.excel.programming
ADG ADG is offline
external usenet poster
 
Posts: 76
Default hide blank rows

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default hide blank rows

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default hide blank rows

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" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Blank Rows TamIam Excel Worksheet Functions 3 May 6th 09 08:42 PM
Auto Hide Blank Rows Gord Dibben Excel Discussion (Misc queries) 0 February 25th 09 09:52 PM
Hide Blank Rows dee Excel Worksheet Functions 2 May 17th 06 11:30 PM
Hide blank rows in a range trussman Excel Programming 0 February 18th 05 03:50 PM
remove or hide blank rows Erik Beijlen via OfficeKB.com Excel Discussion (Misc queries) 1 February 1st 05 02:17 PM


All times are GMT +1. The time now is 07:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"