Solved

VBA delete or insert rows function HELP!

Posted on 2014-04-03
6
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 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