RWayneH
asked on
Delete cell range and shift up
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-
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-
you need to allow for multiple selection rows:
Something like:
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
ASKER
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-
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.
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.
ASKER
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-
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-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Had issues separating out points to two different solutions. Picked the best fitting one to assign points. Thanks both for the help. -R-
Open in new window
Then use the resize to expand out 23 columns to BD
Open in new window
Then use the delete command with the ship up option:
Open in new window
The full code looks like this:
Open in new window
You can then assign a Hot Key to that macro to run it.
-Bear