Solved

VBA delete or insert rows function HELP!

Posted on 2014-04-03
6
387 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 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now