VBA - Code to insert variable number of rows based on cell value not working.

VivJones

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi, looking for some assistance with the below code. I have searched and read through many many threads and posts and tried many many solutions offered to no avail.

I had this working last Friday but it has now ceased working.MPM_CoverSummaryPG1>

I have 2 sheets involved.

The first sheet (MPMFilterAV) is a query based table which is filtered based on drop down selections on another sheet.
The second sheet (MPM_CoverSummaryPG1>) contains the count of rows of the filtered table in cell M1 (AVCount).

If the table has results then I need to insert entire rows below Row 2 on MPM_CoverSummaryPG1> equal to the number in cell M1/AVCount.MPM_CoverSummaryPG1>. At the moment it is inserting 2 rows above Row A. I believe the problem may be in my syntax here: Sheets("MPM-CoverSummaryPG1>").Range("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow, but I am not 100% sure. I have also tried using Rows instead of Range and get the same result.

E.G. If AVCount = 5, then I want to insert 6 rows below row 2 in MPM_CoverSummaryPG1>

VBA Code:
Sub MPMPrev_Click()

Dim numRows As Long

nRows = AVCount + 1

If Sheets("PolicyExtensionsandEndsts").Range("E45") = "Covered" Or Sheets("PolicyExtensionsandEndsts").Range("E45") = "Specified Items" Then
    Sheets("MPM-CoverSummaryPG1>").Activate
    Sheets("MPM-CoverSummaryPG1>").Range("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow
 
    ElseIf Sheets("PolicyExtensionsandEndsts").Range("E45") = "Not Covered" Then
        Sheets("MPM-Cover SummaryPG1>").Rows("1:1").Select
        Selection.Delete
   
End If

End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

VivJones

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I think AVCount is a named range?
nRows = range("AVCount").value + 1
Thanks davesexcel, really appreciate your quick response as time is critical for me on this.

Yes, you are correct AVCount is a named range.

I tried changing nRows as suggested, however get this run-time error on that line:

1608619234997.png

1608619290791.png


Interestingly however, I changed it to
VBA Code:
nRows = Sheets("MPM-Cover SummaryPG1>").Range("M1").Value + 1
and it is working all bar the + 1. Instead of now inserting 2 rows above, it is inserting 5 lines below. For this example MPM-CoverSummaryPG1>, cell M1 = 5, so it should select rows 2 : 8 and then insert 6 rows, however it is only inserting 5 rows.

I have managed a workaround - changing the formula in AVCount to a CountA rather than a Countif, which returns the header row as another row increasing it to 5, so I have just removed the + 1 from the code and it is now achieving what I needed.

Although your suggestion did not do the trick, it gave me enough to investigate further and determine AVCount was returning a 0 value (hence the change in formula), so once again thanks so much for your help.

WORKING CODE is as follows:-

VBA Code:
Sub MPMPrev_Click()

Dim nRows As Long

nRows = Sheets("MPM - Cover Summary PG1>").Range("M1").Value

If Sheets("Policy Extensions and Endsts").Range("E45") = "Covered" Or Sheets("Policy Extensions and Endsts").Range("E45") = "Specified Items" Then
    Sheets("MPM - Cover Summary PG1>").Activate
    Sheets("MPM - Cover Summary PG1>").Rows("2" & ":" & nRows).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrBelow
 
    ElseIf Sheets("Policy Extensions and Endsts").Range("E45") = "Not Covered" Then
        Sheets("MPM - Cover SummaryPG1>").Rows("1:1").Select
        Selection.Delete
   
End If

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top