Hiding Rows with Blank cells
 

I currently have the code below for a button that will unhide last 175 rows
in my worksheet. I now would like to have it hide the rows that are blank in
in the range (C7:BL206) looking from the bottom up. For example, if there
were any values in the cells (from Columns C to K only) in row 190, the macro
would only hide rows 191 thru 206. Any suggestions??

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub

Hiding Rows with Blank cells
 

How about this one?

Sub hideemptyrows()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, Columns.Count).End(xlToLeft).Column _
= 1 Then Rows(i).Hidden = True
Next i
End Sub

or
Sub hideemptyrows()
For i = 206 To 2 Step -1
If Cells(i, Columns.Count).End(xlToLeft).Column _
= 1 Then Rows(i).Hidden = True
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GoBucks" wrote in message
...
I currently have the code below for a button that will unhide last 175 rows
in my worksheet. I now would like to have it hide the rows that are blank
in
in the range (C7:BL206) looking from the bottom up. For example, if there
were any values in the cells (from Columns C to K only) in row 190, the
macro
would only hide rows 191 thru 206. Any suggestions??

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub

Hiding Rows with Blank cells
 

'This subroutine will hide an entire row if the value in a certain column,
'in this case column "T", is blank.
Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "T").Value = "" Then
Rows(RowNdx).RowHeight = 0
End If
Next RowNdx


End Sub
'This macro will hide all of the blank rows in the active
'worksheet or in the selection. If the current selection
'covers more than one row, only blank rows in those rows
'will be deleted. Otherwise, all blank rows in the entire
'worksheet will be deleted. The entire row must be blank
'for the row to be deleted.
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then
Rng.Rows(R).RowHeight = 0
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Don Guillett" wrote:

How about this one?

Sub hideemptyrows()
For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, Columns.Count).End(xlToLeft).Column _
= 1 Then Rows(i).Hidden = True
Next i
End Sub

or
Sub hideemptyrows()
For i = 206 To 2 Step -1
If Cells(i, Columns.Count).End(xlToLeft).Column _
= 1 Then Rows(i).Hidden = True
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GoBucks" wrote in message
...
I currently have the code below for a button that will unhide last 175 rows
in my worksheet. I now would like to have it hide the rows that are blank
in
in the range (C7:BL206) looking from the bottom up. For example, if there
were any values in the cells (from Columns C to K only) in row 190, the
macro
would only hide rows 191 thru 206. Any suggestions??

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub

Hiding Rows with Blank cells
 

Sub HideBlanks()

Dim source As Range
Dim target As Range

Set source = Range("C7:K206")
Set target = source.SpecialCells(xlCellTypeBlanks)
If Not target Is Nothing Then
target.Rows.RowHeight = 0
End If


End Sub

"GoBucks" wrote in message
...
I currently have the code below for a button that will unhide last 175
rows
in my worksheet. I now would like to have it hide the rows that are blank
in
in the range (C7:BL206) looking from the bottom up. For example, if there
were any values in the cells (from Columns C to K only) in row 190, the
macro
would only hide rows 191 thru 206. Any suggestions??

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub

Hiding Rows with Blank cells
 

Sub testing()
Rows(2).Hidden = True
msgbox Rows(2).Height
End Sub

"FatBytestard" wrote in message
...
On Wed, 20 May 2009 16:26:44 +0100, "Patrick Molloy"
wrote:

Sub HideBlanks()

Dim source As Range
Dim target As Range

Set source = Range("C7:K206")
Set target = source.SpecialCells(xlCellTypeBlanks)
If Not target Is Nothing Then
target.Rows.RowHeight = 0
End If


End Sub

Row height zero has the same effect as explicitly setting the row
"hidden"?



"GoBucks" wrote in message
...
I currently have the code below for a button that will unhide last 175
rows
in my worksheet. I now would like to have it hide the rows that are
blank
in
in the range (C7:BL206) looking from the bottom up. For example, if
there
were any values in the cells (from Columns C to K only) in row 190, the
macro
would only hide rows 191 thru 206. Any suggestions??

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:=""
Range("A32:A206").EntireRow.Hidden = False
ActiveSheet.Protect Password:=""
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub