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.
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
' 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