The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Set rngActive = Selection 'ActiveSheet.UsedRange
Set rngNew = rngActive.Resize(rngActive.Rows.Count, 23)
rngNew.Delete (xlShiftUp)
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
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
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.