VBA for Hiding Rows

[SONART]

Hi,

Could someone please help urgently with a VBA to hide/unihide rows.

The range starts from constant row 22 (start row) and ends on constant row 83 (end row).

Between these ranges I would like to only show rows with values.

The number of rows I would like to appear from constant row 22 is derived from cell (A7). The value in this cell changes or dynamic and is determined by a count formula ie. row 22 (start row) + counta(range).

Thanks,
SONART

 

[GTO]

I'm not so sure about this, as I didn't recall hiding rows causing calculation (Anyone wishing to comment?), and my need adjusted to your formula, but try:

Option Explicit
    
Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Rows("22:83").Hidden = True
    Rows("22:" & Cells(7, 1).Value).Hidden = False
    Application.EnableEvents = True
End Sub

The above goes in the Worksheet's Module. Right-Click the sheet's tab, and select View Code.

Hope that helps,

Mark

 

[Rick Rothstein]

The range starts from constant row 22 (start row) and ends on constant row 83 (end row).

Between these ranges I would like to only show rows with values.

Your question was not entirely clear... the ending part was sort of confusing, but I believe the above quoted section indicates what you want to happen. Here is a macro that should do what you want...

Sub HideEmptyRows()
  Dim UnusedColumn As Long
  Const StartRow As Long = 22
  Const EndRow As Long = 83
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Range(Cells(StartRow, UnusedColumn), Cells(EndRow, UnusedColumn))
    .FormulaR1C1 = "=IF(COUNTA(RC[-7]:RC[-1]),"""",""X"")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants).EntireRow.Hidden = True
    .Clear
  End With
End Sub

 

[Trebor76]

Please don't post virtually this same thread from here as it could waste someone's time (as it has mine in the past) if they answer the earlier post unknowing the issue has been resolved here.

 

[SONART]

My apologies Trebor76. This thread is the simplified version or approach of my initial post yesterday. I'll delete it next time.

Thanks GTO your formula works perfectly!!!

Thanks Rick for taking the time to reply.