CODEWITHSUNDEEP
excelvbaexcel-2013
besthost
besthost

Reputation: 888

Cleaning (NOT deleting) cells with a row shift up

To clear patients from a table I use this simple line of code

enter image description here

Selection.Delete Shift:=xlUp

Every time I do this I loose rows at the bottom of the table since it's deleting rows, not clearing values.

Is there any other way to shift the remaining rows up (without affecting my table) after clearing the green ones?

"PAID" is just a simple button for color change within selection.

Upvotes: 1

Views: 2769

Answers (1)

Vulthil
Vulthil

Reputation: 768

This code will remove the contents from the selected row, and move every row under that selected row up. If it encounters an empty row, it will simply stop running.

For clearance purposes you should not select the B column, but only the C to G column.

Sub delete_line()

For i = 1 To 100000
    Selection.ClearContents
    Selection = Selection.Offset(1, 0).Value
    Selection.Offset(1, 0).Select
    If IsEmpty(Selection.Cells(1, 1).Value) = True Then Exit For
Next i

End Sub

Haven't tested it with more than one row selections.

Edit: It won't work if you select more than one row.

Upvotes: 1

Related Questions