Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding blank rows | Excel Programming | |||
Automatically Hiding Blank Rows | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Hiding blank rows | Excel Discussion (Misc queries) | |||
Hiding Rows with Zero or Blank Values | Excel Programming |