?
Solved

VBA delete or insert rows function HELP!

Posted on 2014-04-03
6
Medium Priority
?
405 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:discogs
  • 4
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39976789
Update to:

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")
    Select Case uMethod
Case 1
            dcell.offset(1).resize(uCount).entirerow.delete
Case 2
            dcell.offset(1).resize(uCount).entirerow.insert
end select           

End Sub

Open in new window

0
 

Author Comment

by:discogs
ID: 39976865
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?
0
 

Author Closing Comment

by:discogs
ID: 39976933
Thanks guys. TA
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:nutsch
ID: 39976975
what do you mean by "count the rows of the delete range". Which range are you talking about?
0
 

Author Comment

by:discogs
ID: 39976980
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
0
 

Author Comment

by:discogs
ID: 39976982
You should be able to grab another 500 points on this one champ!

Linked to question ID28404756
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question