Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA delete or insert rows function HELP!

Posted on 2014-04-03
6
Medium Priority
?
402 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

610 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