Auto Un hide Rows

[Cooki]

Hi All

Im after some code that will always make sure that there are 10 rows available from the last row with data on and have the formulas and formatting still in.

I have hidden rows and columns to make it look much neater, but if they need to enter more 10 tasks in, will need to unhide, copy everything down and hide again.

So im hoping it would work like, if they enter 5 more rows of data and save the document, another 5 rows would be added to the bottom which would be total of 10 rows.

Columns go across to R.

Is this possible and if so could someone please help?

 

[Cooki]

These are the columns with the formulas in B,D,E,F,K,L,M,N,O.

Last row is R

And yes correct dont want to copy the hard data lol

 

[Joe4]

OK, go into the VB Editor, make sure VB Project Explorer is open (down left-hand side), find your file, and expand the folders under that file name to expose all the Microsoft Excel Object under it.
The last one should be named "ThisWorkbook". Click on that, and copy/paste the following code into the VB Editor window:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim lr As Long, ls As Long
    Dim snr As Long, enr As Long
    Dim c As Long
    
    Application.ScreenUpdating = False

'   Find last row with data, by looking at column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Find last shown row on sheet
    ls = Cells.SpecialCells(xlCellTypeVisible).Rows.Count
    
'   Find start of new rows to be added
    snr = ls + 1
    
'   Find end of new rows to be added
    enr = lr + 10
    
'   Unhide new rows, if necessary
    If enr > ls Then
        Rows(ls + 1 & ":" & enr).EntireRow.Hidden = False
'       Copy formatting for each column
        For c = 1 To 18
            Cells(ls, c).Copy
            Range(Cells(ls + 1, c), Cells(enr, c)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        Next c
'       Copy formulas down
        Cells(ls, "B").Copy Range(Cells(ls + 1, "B"), Cells(enr, "B"))
        Range(Cells(ls, "D"), Cells(ls, "F")).Copy Range(Cells(ls + 1, "D"), Cells(enr, "D"))
        Range(Cells(ls, "K"), Cells(ls, "O")).Copy Range(Cells(ls + 1, "K"), Cells(enr, "K"))
    End If
    
    Application.ScreenUpdating = True

End Sub

Then, as long as you have VBA enabled, whenever you save the file, it will always make sure to unhide enough rows so that there will be exactly 10 blank rows at the bottom.

 

[Cooki]

OK, go into the VB Editor, make sure VB Project Explorer is open (down left-hand side), find your file, and expand the folders under that file name to expose all the Microsoft Excel Object under it.
The last one should be named "ThisWorkbook". Click on that, and copy/paste the following code into the VB Editor window:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim lr As Long, ls As Long
    Dim snr As Long, enr As Long
    Dim c As Long
   
    Application.ScreenUpdating = False

'   Find last row with data, by looking at column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Find last shown row on sheet
    ls = Cells.SpecialCells(xlCellTypeVisible).Rows.Count
   
'   Find start of new rows to be added
    snr = ls + 1
   
'   Find end of new rows to be added
    enr = lr + 10
   
'   Unhide new rows, if necessary
    If enr > ls Then
        Rows(ls + 1 & ":" & enr).EntireRow.Hidden = False
'       Copy formatting for each column
        For c = 1 To 18
            Cells(ls, c).Copy
            Range(Cells(ls + 1, c), Cells(enr, c)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        Next c
'       Copy formulas down
        Cells(ls, "B").Copy Range(Cells(ls + 1, "B"), Cells(enr, "B"))
        Range(Cells(ls, "D"), Cells(ls, "F")).Copy Range(Cells(ls + 1, "D"), Cells(enr, "D"))
        Range(Cells(ls, "K"), Cells(ls, "O")).Copy Range(Cells(ls + 1, "K"), Cells(enr, "K"))
    End If
   
    Application.ScreenUpdating = True

End Sub

Then, as long as you have VBA enabled, whenever you save the file, it will always make sure to unhide enough rows so that there will be exactly 10 blank rows at the bottom.

Works absolutely perfect

Thank you so much

 

[Joe4]

You are welcome.