Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Blank Rows | Excel Worksheet Functions | |||
Auto Hide Blank Rows | Excel Discussion (Misc queries) | |||
Hide Blank Rows | Excel Worksheet Functions | |||
Hide blank rows in a range | Excel Programming | |||
remove or hide blank rows | Excel Discussion (Misc queries) |