DynamicFill

Fills in more rows at end of a range
Used to add more rows at end to have all formulas, formats and borders for nRows more.
sRange is expected to be 1 row, like B5:P5
Needs functions ColumnName and CutString to work
as usual, caller can customize sheet, workbook and count of rows to fill down.

CodeFunctionName
What is this?

Public

Tested

Original Work
Function DynamicFill(Optional nRows = 50, Optional FillOffset = 1, Optional sRange = "A1:B1", Optional WB = "This", Optional Shee = "Active")
    ' Fills in more rows at end of a range
    ' Used to add more rows at end to have all formulas, formats and borders for nRows more
    ' Will go to last row with data of area A1:B1, then optionally go 1 more row down (or -1 to go up)
    ' Then fill in 50 more rows
    ' Count will be used as CurrentRegion.Rows.Count starting sRange, sRange is expected to be 1 row, like B5:P5
    ' FillOffset = any integer (1, 0 or -1 mostly) Which is if we need to go to 1 more row and fill from there, -1 to go up one row and fill from there, 0 if we do not want to do offset
    '
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    ' Need to cut the column part (letter part) of address
    Col1 = ColumnName(CutString(sRange, , ":"))
    Col9 = ColumnName(CutString(sRange, ":"))
   
    Rows1 = Workbooks(WB).Worksheets(Shee).Range(sRange).CurrentRegion.Rows.Count
    Workbooks(WB).Worksheets(Shee).Range(Col1 & Rows1 + FillOffset, Col9 & Rows1 + nRows + FillOffset).FillDown
End Function

Optional nRows = 50, Optional FillOffset = 1, Optional sRange = "A1:B1", Optional WB = "This", Optional Shee = "Active"

Views 71

Downloads 32

CodeID
DB ID