VBA help - insert row in named range and format paint, delete row from named range

[Jakejake]

Good evening all

I'm relatively new to the world of VBA so please forgive me if I make any mistakes here. I've had a go at this already (I can insert and delete a row, but no other logic to specify where to insert the row, how to format it etc...), but the more intricate parts are where it all falls down so any help is much appreciated.

In short I am trying to make two macros (which I will attach to buttons for a user to click). One button inserts a row inside an existing Range, the second button deletes a row inside an existing range. However, there are additional complexities beyond this.

Structure of the range.
At any one time, my data range will be at least 4 rows long. This includes a header row, a footer row and two rows for data. The range should be able to grow (additional data rows) but there should always be a header at the top and a footer at the bottom.

Macro 1 - Insert
I'd like to be able to insert a row at the bottom of my range, just above the footer row. So, in the example of four existing rows (Header, Data1, Data2, Footer) I would like this row to always go just before the footer (Header, Data1, Data2, NEW ROW, footer).

I would also like the macro to then copy the formatting (format paint) from the entirety of data1, then paste this formatting over all data rows, including the row the formatting was copied from. So in the example of (Header, data1, data2, NEW ROW, footer) I would like the VBA to copy formatting from the data1 row, and paste it all over data1, data2 and NEW ROW.

This entire thing should be endlessely repeatable, one row at a time.


Macro 2 - Delete
I'd like to have a button that would delete the entire row directly above footer. So in the example (Header, Data1, Data2, NEW ROW, footer) it would entirely delete NEW ROW. However, if the total range is just four rows, the deletion should not operate at all. I.e. the range can never be less than four rows (header, data1, data2, footer).

For this example by worksheet is called Test_Sheet and the range is named Test_Range.

This is making my head spin. I'm assuming I need the VBA to constantly count how many rows are currently in the range and adjust things accordingly, but that's easier said than done.

Book1
	A	B	C	D	E	F
1
2	Title 1	Title 2	Title 3	Title 4	Title 5
3	04-May-20	Test 1	Test 1	Test 1	Test 1
4	04-May-20	Test 1	Test 1	Test 1	Test 1
5
6
Test_Sheet

 

[John_w]

Try these:

Public Sub Insert_Row()

    With Range("Test_Range")
        .Offset(.Rows.Count - 1).Resize(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Offset(1).Resize(1).Copy
        .Offset(1).Resize(.Rows.Count - 2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
    
End Sub


Public Sub Delete_Row()

    With Range("Test_Range")
        If .Rows.Count > 4 Then .Offset(.Rows.Count - 2).Resize(1).Delete Shift:=xlUp
    End With

End Sub

 

[Jakejake]

Try these:

Public Sub Insert_Row()

    With Range("Test_Range")
        .Offset(.Rows.Count - 1).Resize(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Offset(1).Resize(1).Copy
        .Offset(1).Resize(.Rows.Count - 2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
   
End Sub


Public Sub Delete_Row()

    With Range("Test_Range")
        If .Rows.Count > 4 Then .Offset(.Rows.Count - 2).Resize(1).Delete Shift:=xlUp
    End With

End Sub

Flawless and far neater that what I've been struggling with.

Worked first time.

Thank you so much for saving me a headache.

 

[Jakejake]

Is there a way to modify this / bolt on a section that will allow me to autofill down a formula?

Let's assume a formula is pre-populated in an Excel cell already.

Specifically, within my named range, the cell with the formula that I would like to copy is 6 columns in, 1 row down. I would like to autofill it to the bottom of the range, excluding the very last row.

Any idea how?

 

[Jakejake]

Is there a way to modify this / bolt on a section that will allow me to autofill down a formula?

Let's assume a formula is pre-populated in an Excel cell already.

Specifically, within my named range, the cell with the formula that I would like to copy is 6 columns in, 1 row down. I would like to autofill it to the bottom of the range, excluding the very last row.

Any idea how?

Le me re-word this.

Public Sub Insert_Row() currently works flawlessly, thank you.

However, I now have an additional requirement for one specific column within the range. Let's assume cell E3 in the above extract needs to be autofilled down the range (to one from the last row in range) whenever "Insert_Row" is run.

Essentially, there is a formula in E3 that needs to be copied down each time a row is inserted. However, the range can be located in multiple places on the sheet so I guess Ineed to use offset, but this is where I fall apart.

 

[John_w]

Let's assume a formula is pre-populated in an Excel cell already.

Specifically, within my named range, the cell with the formula that I would like to copy is 6 columns in, 1 row down. I would like to autofill it to the bottom of the range, excluding the very last row.

It can be quite tricky using the Offset and Resize properties of a Range to address the cell(s) you want. There is also the Item(row,column) property which refers to a specific cell in a Range, e.g. theRange.Item(1,1) is the top-left cell of theRange and theRange.Item(1,6) is the cell in the 1st row and 6th column of theRange. I always use the Address property to know the exact cell(s) which the combination of Offset and/or Resize and/or Item is referring to.

If I understand you correctly, you want the cell in the 6th column of the named range to be autofilled from the row above the newly inserted row to the newly inserted row. See if this does what you want - I've included debugging to show the various cell(s) being addressed in the named range.

Public Sub Insert_Row()

    With Range("Test_Range")
        Debug.Print "Range address " & .Address, "Num rows " & .Rows.Count, "Start row " & .Row
        Debug.Print "Insert row at " & .Offset(.Rows.Count - 1).Resize(1).Address
        Debug.Print "1st row, 6th column cell " & .Item(1, 6).Address
        .Offset(.Rows.Count - 1).Resize(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Debug.Print "Address of cell in 6th column of row above inserted row " & .Item(.Rows.Count - 2, 6).Address
        Debug.Print "Autofill from " & .Item(.Rows.Count - 2, 6).Address & " to " & .Item(.Rows.Count - 2, 6).Resize(2).Address
        .Item(.Rows.Count - 2, 6).AutoFill Destination:=.Item(.Rows.Count - 2, 6).Resize(2), Type:=xlFillDefault
        Debug.Print "Copy first data row " & .Offset(1).Resize(1).Address
        Debug.Print "Paste to " & .Offset(1).Resize(.Rows.Count - 2).Address
        .Offset(1).Resize(1).Copy
        .Offset(1).Resize(.Rows.Count - 2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
    
End Sub

If that doesn't do what you want, perhaps post a screen shot showing the named range before and after inserting the new row and autofill would help.

 

[Jakejake]

Ok,

It can be quite tricky using the Offset and Resize properties of a Range to address the cell(s) you want. There is also the Item(row,column) property which refers to a specific cell in a Range, e.g. theRange.Item(1,1) is the top-left cell of theRange and theRange.Item(1,6) is the cell in the 1st row and 6th column of theRange. I always use the Address property to know the exact cell(s) which the combination of Offset and/or Resize and/or Item is referring to.

If I understand you correctly, you want the cell in the 6th column of the named range to be autofilled from the row above the newly inserted row to the newly inserted row. See if this does what you want - I've included debugging to show the various cell(s) being addressed in the named range.

Public Sub Insert_Row()

    With Range("Test_Range")
        Debug.Print "Range address " & .Address, "Num rows " & .Rows.Count, "Start row " & .Row
        Debug.Print "Insert row at " & .Offset(.Rows.Count - 1).Resize(1).Address
        Debug.Print "1st row, 6th column cell " & .Item(1, 6).Address
        .Offset(.Rows.Count - 1).Resize(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Debug.Print "Address of cell in 6th column of row above inserted row " & .Item(.Rows.Count - 2, 6).Address
        Debug.Print "Autofill from " & .Item(.Rows.Count - 2, 6).Address & " to " & .Item(.Rows.Count - 2, 6).Resize(2).Address
        .Item(.Rows.Count - 2, 6).AutoFill Destination:=.Item(.Rows.Count - 2, 6).Resize(2), Type:=xlFillDefault
        Debug.Print "Copy first data row " & .Offset(1).Resize(1).Address
        Debug.Print "Paste to " & .Offset(1).Resize(.Rows.Count - 2).Address
        .Offset(1).Resize(1).Copy
        .Offset(1).Resize(.Rows.Count - 2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
   
End Sub

If that doesn't do what you want, perhaps post a screen shot showing the named range before and after inserting the new row and autofill would help.

Ok this seems beyond my current level of understanding but it does work, so I daren't question it, thank you!!

The only slightly weird behaviour is that it doesn't seem to "insert" a row in the traditional sense, in that normally everything gets pushed down one row.

This code seems to leave all the column heights exactly as they are currently formatted, and the code pushes the new cells on top of these. I.e. if row 20 is 60 pixels high and I insert a row above this normally, row 21 is now 60 pixels high. However, using this code it's always row 20. It doesn't get shifted. Gives the impression of wider rows "climbing" up the page!!

Not a massive issue, but certainly curious.