Is there a way to delete the current range selected in VBA?

Question

I am trying to select a specific range of data and delete only the cells I identify, not the entire row.

I have included the coding that I currently have. I am trying to select everything to the left of the indicated cell, delete the range, and shift all cells up. I cannot input a specific range (ie. Range("B3:B7").delete, etc.) as the range will be changing throughout the code. I need it to be a dynamic range that will change as the code runs.

  Worksheets("Sheet1").Select
    Cells(2, 6).Select

    ActiveCell.Offset(0, 1).Select
    col = ActiveCell.Column
    row = ActiveCell.Row

    Cells(row, col).Select
    Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
    Range.Delete Shift:=xlToUp

Let me know if you need any more information. Code will run up until I hit the last line (Range.Delete).

Thanks in advance.

`Selection.Delete`.... but see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) as you don't need/shouldn't use `Select` :) — BigBen, Jan 14 '20 at 18:36

Peyter · Answer 1 · 2020-01-14T18:54:11.887

I can't write it for you. But consider this technique since you seem to be doing this manually. Application.Inputbox is a built in userform that pauses the code execution until you review the range / craft your own selection.

Dim xrng As Range
Dim rngholder As Range
Dim xArray(0 To 20) As Variant
Dim x As Integer 

Set xrng = Application.Selection
Set xrng = Application.InputBox("Title", "Make a selection", xrng.Address, Type:=8)
    x = 0
'If xrng = "" Then Exit Sub

For Each rngholder In ActiveSheet.Range(xrng.Address)

    If rngholder.Value > "" Then
        xArray(x) = VBA.Trim(rngholder.Value)
    Else
    End If
x = x + 1
Next rngholder

In this case the Inputbox is loaded with the active cell or whatever the selection was when the macro was called and the range is populated into an array. Where you can customize this is on the line set 'xrng =' line. I would put 'set xrng = the logic to get that selection you've described so everything to the left, and up, and delete it.

edit:

Set xrng = Range(ActiveCell, Range(ActiveCell.End(xlToLeft), ActiveCell.End(xlUp))).Select

You can figure this out with a little more research into ranges. If you chose this answer you'll have an interface to handle exceptions manually, and since it seems to me you're doing this somewhat by eye, its a compromise you might benefit from in actual use.

score 0 · Accepted Answer · answered Jan 14 '20 at 19:42

I think this is what you are looking for. When you select any single cell, this line of code will select the range from column A and the active row, to the active column + 1 on the active row.

 ThisWorkbook.Sheets("Sheet1").Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, ActiveCell.Column + 1)).Delete

Is there a way to delete the current range selected in VBA?

2 Answers2