Link to home
Create AccountLog in
Avatar of KP_SoCal
KP_SoCalFlag for United States of America

asked on

VBA to delete rows

Attached is a spreadsheet containing a sub routine that inserts a given number of rows on the selected worksheets (worksheets are selected in the code).

Upon clicking the "Click to Insert" button, you will receive a dialog box that will allow you to input the number of rows you wish to insert.  I'd like to modify this module so that when a negative value is entered, it will delete rows as opposed to inserting.

Any suggestions?
Example-Code.xlsm
SOLUTION
Avatar of krishnakrkc
krishnakrkc
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dsacker
I looked at your spreadsheet. There may be a few other places you need to deal with deleting rows, but it will start with changing your Selection.Insert to the following:
    If rng > 0 Then
        Selection.Insert
    Else
        If rng < 0 Then
            Selection.Delete
        End If
    End If

Open in new window

Like I said, you may need to deal with the line that says: ws.Cells(sel - 1, 1).Resize(Abs(rng) + 1, 1).EntireRow.FillDown

Notice I added the ABS function.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of KP_SoCal

ASKER

I'm going to go with Andrews solution.  It accomplishes what I need.  Thanks for your help!

kp
Hi

BTW, is there anything new in Andrews' solution as compared to my reply ?

Kris, my apologies.  After looking at your recommendation, it appears to have accomplished the exact same thing.  This difference between your recommendation and Andrew's is very minimal.  Sorry, I should I awarded you the points since you responded first.  When I saw a slight difference, I defaulted to Andrew's since I borrowed the code from a different thread he provided a solution to.


No worries :)

Thank you.  I submitted the request to reopen anyways.  As an FYI, I posted a separate question that uses this code if you're interested in checking it out.  
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27426194.html
The reason I posted here was in my test @krishnakrkc's code did not delete the rows from all selected worksheets, which I believed was a requirement, and does not adjust the 3d range formual on the summary sheet.  I wasn't trying to duplicate what he had done.  The key difference is the explcit Select in the delete part of the code.

I don't think the select is required to delete the rows.
It shouldn't be but it is required when a group of sheets is selected like this.  Try it - your code does not delete rows from all the grouped worksheets, only the first one.  I'm not worrying about points here - I'm happy if @KP_SoCal want to reallocate, I was just pointing out that my solution was distinct,

fair enough :)