VBA Macro to Insert Rows Below ActiveCell with Formatting

[cjohnson0181]

All, I have started a code that inserts n-number of rows below based on a dialog box. Now I want to format all of the rows added with all thin borders and then thick outside borders. Any help would be GREATLY appreciated as I am under a time crunch!

Sub insertMultipleRows()


Dim iCountRows As Integer


    iCountRows = Application.InputBox(Prompt:="How many rows do you want to add? Starting with row " _
        & ActiveCell.Row & "?", Type:=1)
        
        'Error Handling
        If iCountRows <= 0 Then End
    
    If ActiveCell.Row < 9 Then
        MsgBox "You Cannot Insert Rows Before Line 9, Dummy!"
        
    Else
    
    'Based on number of rows specified, inser these rows
    Rows(ActiveCell.Row & ":" & ActiveCell.Row + iCountRows - 1).Insert shift:=xlDown
    
    'Formatting
    With ActiveCell.Columns("A:AG").Select
    Selection.Borders.Weight = xlThin
    
    ActiveCell.Columns("A:AG").Select
    Selection.Borders(xlEdgeBottom).Weight = xlMedium
    
    ActiveCell.Columns("A:AG").Select
    Selection.Borders(xlEdgeTop).Weight = xlMedium
  
    ActiveCell.Columns("A:AG").Select
    Selection.Borders(xlEdgeRight).Weight = xlMedium
    End With
    
    
    End If
        
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub insertMultipleRows()


   Dim iCountRows As Integer
   Dim Rng As Range
   
   iCountRows = Application.InputBox(Prompt:="How many rows do you want to add? Starting with row " _
      & activecell.Row & "?", Type:=1)
   
   'Error Handling
   If iCountRows <= 0 Then Exit Sub
   
   If activecell.Row < 9 Then
      MsgBox "You Cannot Insert Rows Before Line 9, Dummy!"
      Exit Sub
   End If
   
   Set Rng = Range("A" & activecell.Row)
   Rng.Resize(iCountRows, 33).EntireRow.Insert
   Set Rng = Range("A" & Rng.Row - iCountRows).Resize(iCountRows, 33)
   With Rng
      .Borders.Weight = xlThin
      .BorderAround , xlMedium
   End With
       
End Sub

 

[cjohnson0181]

Thanks! I can use this to apply to other macros that I currently use which are not as efficient. In particular I am having a very difficult time with call statements such as activecell vs range, etc.

However, I would also like to add the xlMedium border between the rows? Currently, if you add three rows then the medium outside border is around all three whereas I need it to be around the three individual cells. I'm sure this is an easy fix. Thanks in advance!

 

[Fluff]

This will put a medium border on each row

   With Rng
      .Borders.Weight = xlThin
      .Borders(xlInsideHorizontal).Weight = xlMedium
      .BorderAround , xlMedium
   End With

 

[cjohnson0181]

Absolutely perfect! Already updated the other codes with similar formatting!

 

[Fluff]

Glad to help & thanks for the feedback