Solved

Delete cell range and shift up

Posted on 2013-05-28
7
227 Views
Last Modified: 2013-05-30
Looking for a macro that will do the following.

On the active sheet tab, after selecting a series of rows in column AG, (example AG96 thru AG105, but could be any row or series of rows), then select all the columns from AG:BD of those selected rows and delete them, shifting up.

This would allow me to create a Ctrl+ hotkey to do this instead of manually highlighting the range, etc…

Thanks -R-
0
Comment
Question by:RWayneH
  • 3
  • 2
  • 2
7 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39203364
What you need to do in your macro is get the active selection
    Set rngActive = Selection 'ActiveSheet.UsedRange

Open in new window


Then use the resize to expand out 23 columns to BD
    Set rngNew = rngActive.Resize(rngActive.Rows.Count, 23)

Open in new window


Then use the delete command with the ship up option:
    rngNew.Delete (xlShiftUp)

Open in new window


The full code looks like this:
Sub ExpandSelection()
 
    ' Get Access to Current Selection
    Dim rngActive As Range
    Set rngActive = Selection 'ActiveSheet.UsedRange
    
    ' Set up The New Range - expand out 23 columns
    Dim rngNew As Range
    Set rngNew = rngActive.Resize(rngActive.Rows.Count, 23)
    rngNew.Select
    
    ' Delete The Selection moving the rows up
    rngNew.Delete (xlShiftUp)
    
End Sub

Open in new window


You can then assign a Hot Key to that macro to run it.

-Bear
0
 
LVL 24

Expert Comment

by:Steve
ID: 39203808
you need to allow for multiple selection rows:

Something like:
Sub ExpandSelection()
 
    ' Get Access to Current Selection
    Dim rngActive As Range ' Variable to hold current selection
    Dim rngNew As Range ' variable to hold each area once expanded
    Dim rngDelete As Range ' vaiable to hold the joined expanded areas for deletion
    
    Set rngActive = Selection 'fill current selection vaiable

    For Each a In rngActive.Areas ' for each area in selection (each seperate set of rows selected)
    
    ' Set up The New Range for the area - expand out 23 columns

    Set rngNew = a.Resize(a.Rows.Count, 23)
    
        rngNew.Select

    'fill the delete range with each new range once expanded:
        If rngDelete Is Nothing Then
            Set rngDelete = rngNew
        Else
            Set rngDelete = Application.Union(rngNew, rngDelete)
        End If
    
    Next a

        rngDelete.Select
        ' Delete The Selection moving the rows up
        rngDelete.Delete (xlShiftUp)

End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39204385
Thanks..  this multiple selection is an awesome idea!!  Can you expand on how the multiple selection works?  Is this hold the ctrl key and select more than one range in target column?  Perhaps flagging the cell ranges with a cell format would work better.  Than I could go down the column, flag all the ranges and then run the multi selection macro.  Would that be a problem to adj the code to look for any cell formatting in column AG?  (even if the selection is one row)   -R-
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Steve
ID: 39205002
As you say, if you hold ctrl and select more than one set of rows (not contiuous) the use of the following two options...

1) "Areas" which are the individual groups of ranges selected in "selection"
2) "Union" which joins the resized ranges back together.

The unioned range is then deleted in one go.

You could use formatting... or you could just type "D" into the cells in the column and use that as a selection criteria. but just selecting the rows may be easier, is all about how you wish to work.
0
 

Author Comment

by:RWayneH
ID: 39205090
Could you please add the search for cells that have any type of cell format applied to the cell in column AG?  While keeping the multi select option intact?  If any cell has cell formatting in column AG delete the range or if it is selected?  The use is that we will format numerous row in column AG and we want to delete those all at once or just delete selected.

Is there a way to edit the suggested solution to do this?  True select and Ctrl+ select and Ctrl+ does move fast, but would also like the option of the cell format delete too, keeping both options available for use I believe would be best. -R-
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 39206602
If you want both options I suggest two different Macros.  First use Barman's improvement on my code.  

Now on the search for formatted cells are you looking for a specific format or jut anything that is not in the general format?  The script below will look at all rows in column AG.  If they are not in the General format it will delete that row out to column BD.

Sub DeleteByFormat()
    Dim rngCurrent As Range ' Variable to hold the current selection
    Dim rngNew As Range ' variable to hold each area once expanded
    Dim rngDelete As Range ' variable to hold the joined expanded areas for deletion
    
    For Each rngCurrent In Range("AG1", Range("AG1").End(xlDown).Address)
        ' If the format is not general - Select for Deletion
        If rngCurrent.NumberFormat <> "General" Then
            ' Expand the selection out to colum BD
            Set rngNew = rngCurrent.Resize(rngCurrent.Rows.Count, 23)
            
            rngNew.Select
        
            ' Keep Track of all the selected rows
            If rngDelete Is Nothing Then
                Set rngDelete = rngNew
            Else
                Set rngDelete = Application.Union(rngNew, rngDelete)
            End If
        End If
    Next
    
    rngDelete.Select
    ' Delete The Selection moving the rows up
    rngDelete.Delete (xlShiftUp)

End Sub

Open in new window


-Bear
0
 

Author Closing Comment

by:RWayneH
ID: 39207502
Had issues separating out points to two different solutions.  Picked the best fitting one to assign points.  Thanks both for the help. -R-
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 4 28
Vlookup nonexact IP address match 32 53
Error in formula not increasing value 2 15
Formula for time spans 10 23
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

863 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