[Excel] adding a row to spreadsheet want to update my code

[sarahmay]

Hi all

I'm not confident with VBA as yet and have simply been creating macro's and then found the need to edit them, so am gradually exploring VBA and what can be done with it.

I currently have a spreadsheet with column A containing staff names. I then have calculations set up to track their working patterns.
I have created a macro that inserts a column at column f and then updates all of my formulae accordingly for example range (d3:d123).

However, when I add or remove staff this changes the final row reference by one or two digits and I want to know if there is a way that my code re-written to update automatically and amend my range:

here is my code, highlighted are the bits that are variable:

 
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Range("G2").Select
    Selection.AutoFill Destination:=Range("F2:G2"), Type:=xlFillDefault
    Range("F2:G2").Select
    Columns("F:F").EntireColumn.AutoFit
      
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(RC[4]:RC[17])"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B123"), Type:=xlFillDefault
    Range("B4:B123").Select
       Range("D4").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(RC[2]:RC[29])"
    Range("D4").Select
    Selection.AutoFill Destination:=Range("D4:D123"), Type:=xlFillDefault
    Range("D4:D123").Select
    
    Range("H2").Select
    Selection.AutoFill Destination:=Range("G2:H2"), Type:=xlFillDefault
    Range("G2:H2").Select
    Columns("G:G").Select
    Selection.Copy
    Columns("F:F").Select
    
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
     Range("G124").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("F124:G124"), Type:=xlFillDefault
    Range("F124:G124").Select
       Range("F4").Select
End Sub

Thank you all in advance

ETA a bit of gratitude goes a long way

[vwgtir32vr6]

first of all, a little tip: whenever you have code that selects something and then does something to that selection, you can omit the selection (in fact it will speed up your routines quite a bit i think). for example:

Columns("F:F").Select
Selection.Insert Shift:=xlToRight

would be:

Columns("F:F").Insert Shift:=xlToRight

(i think this will work, i have no time to test it)

as for your question, i might use the .UsedRange property to the get the lowest used row on your worksheet. this will only work if the lowest filled cell on your worksheet is part of the range you want to consider. Otherwise, you could use .End(xlDown) from the top if you wanted find the row of the last used cell. hth

[dmaruca]

Sarah, I have edited what you posted to what should be a working macro. However, it is still using some static offsets "RC[2]:RC[29]" and I don't know what these are referring to.

I can see that you are just using the record macro feature and checking it out. Have you had any programming experience before now?

Sub lkjslkfjd()

    Dim EndRange As Range
    Dim LastRow As String
    Dim s As String
    
    Columns("F:F").Select
    
    'This will return the last row used.
    'ASSUMES: All values are filled in starting with row 1 and
    '         there are no empty cells all the way to the end.
    Set EndRange = Selection.End(xlDown)
    LastRow = CStr(EndRange.Row)
    
    Selection.Insert Shift:=xlToRight
    Range("G2").Select
    Selection.AutoFill Destination:=Range("F2:G2"), Type:=xlFillDefault
    Range("F2:G2").Select
    Columns("F:F").EntireColumn.AutoFit
      
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(RC[4]:RC[17])"
    
    s = Replace("B4:B?", "?", LastRow)
    Selection.AutoFill Destination:=Range(s), Type:=xlFillDefault

    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(RC[2]:RC[29])"
    
    s = Replace("D4:D?", "?", LastRow)
    Selection.AutoFill Destination:=Range(s), Type:=xlFillDefault
    
    Range("H2").Select
    Selection.AutoFill Destination:=Range("G2:H2"), Type:=xlFillDefault
    
    Columns("G:G").Select
    Selection.Copy
    Columns("F:F").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("G" & LastRow).Select
    s = Replace("F?:G?", "?", LastRow)
    Selection.AutoFill Destination:=Range(s), Type:=xlFillDefault
    Range("F4").Select
End Sub

[sarahmay]

Hi all,

Thank you for your replies, I shall try those over the next couple of days and report back.

Dmaruca: I have no programming experience, I am just trying to make the most of the macro that I originally recorded and adding/editing it as I learn a little more. I don't think a couple of years of basic count as that was 20 years ago

Sarah

[dmaruca]

The VBA language itself doesn't take long to get a grasp of, but don't be surprised if you're still learning some nuances years down the line. The part that may be the most overwhelming at first is learning how to deal with the number of objects you have to learn to use to become effective at different tasks. For Excel, I would recommend just the help file that it comes with (From the VB IDE; Help->Visual Basic Help->Table of Contents->Excel Visual Basic Reference->(?) Microsoft Excel Object Model). You'll mostly deal with Range, Workbook, and Worksheet.

Learn to use the Object Browser. It is your best friend. It's on the toolbar between properties and the help button.

Good luck!