Dynamic Table Hide Rows

[CJG19]

Good Morning!

I wonder if someone can help

I am trying to select all rows in a table not including the header/title row and the last row. The data in my table underneath the header/title row starts in cell A4 for context. I have the below code which I have been working on, but it seems to be selecting to row 46, but my last row (which I still want showing is currently row 8). This is a table that will be added to, so the code needs to find the last row, go up one row and select from there to the header/title row. (There is nothing at all below row 8, I have performed a 'Clear All' just in case).

Dim sht As Worksheet
Dim LR As Long
Dim Lc As Long
Dim FirstCell As Range

    Set sht = Worksheets("Sheet 4")
    Set FirstCell = Range("A4")
    LR = FirstCell.SpecialCells(xlCellTypeLastCell).Row
    Lc = FirstCell.SpecialCells(xlCellTypeLastCell).Column
    sht.Range(FirstCell, sht.Cells(LR - 1, Lc)).Select
    Selection.EntireRow.Hidden = True

Any help would be very much appreciated

Kind Regards,

CJG19

 

[Jeffrey Mahoney]

Please try this

Sub SelectMidRange()

  Dim sht As Worksheet
  Dim LR As Long
  Dim Lc As Long
  Dim FirstCell As Range
  Dim LastCell As Range
  

  Set sht = Worksheets("Sheet4")
  Set FirstCell = sht.Range("A4")
  Set LastCell = sht.Cells(sht.Cells.Rows.Count, 1).End(xlUp)
  sht.Range(FirstCell.Offset(1, 0), LastCell.Offset(-1, 0)).EntireRow.Select
  Selection.EntireRow.Hidden = True
  
End Sub

 

[CJG19]

Amazing, thank you so much Jeffrey!