asked on

VBA delete or insert rows function HELP!

Hi guys,

This may seem a little obvious but I am wanting some assistance in modifying some code that is intended to dynamically insert or delete rows in an excel workbook based on a couple of parsed variables which are:

1. Row count - i.e. I already know how many rows to add or delete;
2. Method - i.e. either 1. delete or 2. insert.

My current code is:

Sub delins(uCount As Integer, uMethod As Integer)
    'Inserts or deletes a number of given rows based on count and rangename
    'Method definition
    '[1] = Delete
    '[2] = Insert
    
    Dim i As Integer, n As Integer, m As Long, wks As Worksheet
    Set wks = ThisWorkbook.Sheets("Test")
        
    Set dCell = Range("testrow")
    Do While uCount > 0
        n = dCell - 1
        m = uRowNumber
        If n > 0 Then
            Rows(m + 1 & ":" & m + n).Insert
            Set dCell = dCell.Offset(n + 1, 0)
        Else
            Set dCell = dCell.Offset(1, 0)
        End If
    Loop
End Sub

Open in new window

Essentially, when I call the function, if the method used is to delete, the function will delete x number of rows below the cell containing my range name. In opposite (insert), the function will insert x number of rows below my range name.

Any help appreciated.
TA

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

discogs

ASKER

Hi there,

Thanks this works a treat.

One more thing if you don't mind. Is there a way I can actually count the rows of the delete range within the same routine as well? I can change the rowcount to optional in the inputs.

Just to make my code my efficient?

discogs

ASKER

Thanks guys. TA

nutsch

what do you mean by "count the rows of the delete range". Which range are you talking about?

discogs

ASKER

I have a named range (TestRow) which is one cell only.

I used TestRow as a marking point to populate my data which grows and shrinks depending on the records I need to fill it with.

For the populate component, I already know my record count from data extract, so I can insert x rows to allow for it without affecting the formatting etc.

For the delete component, (which is where I need help) I am wanting to count the number of rows used from range/cell TestRow downwards to the bottom of my data. This is my way of working out how many rows I need to delete before populating a new data set.

I hope I make sense. Let me know if it is not clear.
TA

discogs

ASKER

You should be able to grab another 500 points on this one champ!

Linked to question ID28404756