Deleting row elements except column1 and then shift cells up for the rest

user_10579300 0
Viewed 116

Is there any way to delete all the row elements except the row element in column A and then shift cells up only for the deleted row using VBA coding. Right now I manually select a single row except the row element in column A and then delete using ctrl+- and select shift cells up.

I have also tried this

Worksheets("sheet1").Rows(i).Delete Shift:=xlShiftUp

but it deletes the whole row.

vba excel
1 Answers

You will need to specify the range excluding that column e.g.

Worksheets("sheet1").Range("B1:D1").Delete Shift:=xlUp

You could also specify starting from B2, for example, and dynamically finding last column in same row:

With Worksheets("Sheet1")
   .Range(.Cells(2, 2), .Cells(2, .Cells(2, .Columns.Count).End(xlToLeft).Column)).Delete Shift:=xlUp
End With

To make that a little more easily amendable you can use Constants and you should have a test that lastColumn is > than START_COLUMN or you may delete the keep column.

Const ROW_OF_INTEREST As Long = 2
Const START_COLUMN As Long = 2
Dim lastColumn As Long

With Worksheets("Sheet1")
    lastColumn = .Cells(ROW_OF_INTEREST, .Columns.Count).End(xlToLeft).Column
    If lastColumn > START_COLUMN Then
        .Range(.Cells(ROW_OF_INTEREST, START_COLUMN), .Cells(ROW_OF_INTEREST, .Cells(ROW_OF_INTEREST, .Columns.Count).End(xlToLeft).Column)).Delete Shift:=xlUp
    End If
End With
user_62412350
Related