+ Reply to Thread
Results 1 to 2 of 2

Macro to expand formulas to additional rows

  1. #1
    Sharon P
    Guest

    Macro to expand formulas to additional rows

    I'm working with a large multi-sheet workbook and need to expand the formulas
    on many sheets from Row 2 to a variable number of rows. Can someone offer
    vba code that will allow me to prompt for the variable and then expand the
    formulas to the specific number of rows accordingly?

    I'm trying to keep my spreadsheet lean and don't want to have 750 rows of
    formulas sitting there mostly unused. Sometimes, I'll only have 5 rows of
    data and
    other times 750.

    My knowledge of vba is second grade so any documentation in the code itself
    indicating what's going on is most encouraging. Thanks!

  2. #2
    Arvi Laanemets
    Guest

    Re: Macro to expand formulas to additional rows

    Hi

    An example here:

    Public Sub RedesignTables()

    ' removing passwords
    Sheets("MySheet1").Unprotect Password:="*****"
    Sheets("MySheet2").Unprotect Password:="*****"
    ....
    ' redesigning MySheet1
    Worksheets("MySheet1").Activate
    Worksheets("MySheet1").Range("A2").Sort _
    Key1:=Worksheets("MySheet1").Range("A2"), Order1:=xlAscending, _
    Header:=xlGuess
    varEntries = [MySheet1_Entries]
    varRows = [MySheet1_Rows]
    varPrepared = [MySheet1_Prepared]
    n = varRows - varEntries - varPrepared
    ' checking the number of rows, adding/removing them
    If n > 0 Then
    ' remove abundant prepared rows
    Worksheets("MySheet1").Rows((varEntries + 2) & ":" & (varEntries + n
    + 2)).Delete Shift:=xlUp
    ElseIf n < 0 Then
    ' add missing prepared rows
    Do Until n = 0
    Worksheets("MySheet1").Rows((varEntries + 2) & ":" & (varEntries
    + 2)).Insert Shift:=xlDown
    Worksheets("MySheet1").Range("A" & (varEntries + 3) & ":D" &
    (varEntries + 3)).Copy
    Worksheets("MySheet1").Range("A" & (varEntries + 2) & ":D" &
    (varEntries + 2)).PasteSpecial Paste:=xlFormulas
    n = n + 1
    Loop
    End If
    Worksheets("MySheet1").Range("A2").Select

    ' redesigning MySheet2
    Worksheets("MySheet2").Activate
    ...
    ' restore passwords
    Sheets("MySheet1").Protect Password:="*****", UserInterfaceOnly:=True
    Sheets("MySheet1").EnableAutoFilter = True
    Sheets("MySheet2").Protect Password:="*****", UserInterfaceOnly:=True
    Sheets("MySheet2").EnableAutoFilter = True
    ...
    End Sub

    The named ranges defined in workbook, and used by procedure:
    MySheet1_Entries=COUNTA(Articles)
    Articles=OFFSET(MySheet1!$A$2;;;COUNTIF(MySheet1!$A:$A;"<>")-1;1)
    MySheet1_Rows=COUNTIF(MySheet1!$D:$D;"<>")-1
    MySheet1_Prepared=SetUp!$B$11

    NB! - the number of prepared empty rows for every sheet is stored on sheet
    SetUp. The column D on MySheet1 contains formulas (others may contain too -
    I used this column to determine the total number of rows (filled+prepared)
    in table.


    --
    Arvi Laanemets
    ( My real mail address: arvi.laanemets<at>tarkon.ee )



    "Sharon P" <[email protected]> wrote in message
    news:[email protected]...
    > I'm working with a large multi-sheet workbook and need to expand the
    > formulas
    > on many sheets from Row 2 to a variable number of rows. Can someone offer
    > vba code that will allow me to prompt for the variable and then expand the
    > formulas to the specific number of rows accordingly?
    >
    > I'm trying to keep my spreadsheet lean and don't want to have 750 rows of
    > formulas sitting there mostly unused. Sometimes, I'll only have 5 rows of
    > data and
    > other times 750.
    >
    > My knowledge of vba is second grade so any documentation in the code
    > itself
    > indicating what's going on is most encouraging. Thanks!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1