Solved: MOVE ROWS UP TO BLANK ROW

[BENSON]

The code below clears the contents of a row of data if a speific name is selected.What I am looking for, is a code to be added that once a row is cleared all the remaining data below the cleared row will move up to the vacant row, hence leaving no gaps or blank rows in the data range.The range is "A7:A39"

thanks


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, Worksheets("TS GD").Columns(1), 0)
Worksheets("TS GD").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[p45cal]

Would replacing the line
Worksheets("TS GD").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
with
Worksheets("TS GD").Rows(RowNum).Delete Shift:=xlUp
do it?
(It deletes the whole row, while your code only clears certain cells.)

[BENSON]

No I dont want to delete the rows as I which to keep the size of the range constant

[p45cal]

Is the range really only one column wide ("A7:A39")?
Is that range a Name too?

Assuming it's the whole row, would this work: With Target
RowNum = Application.Match(.Value, Worksheets("TS GD").Columns(1), 0)
Worksheets("TS GD").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("TS GD").Rows(RowNum).Cut
Worksheets("TS GD").Rows(40).Insert Shift:=xlDown
End With
(If the range is also a Name then the Name will be one row short after this operation.

[BENSON]

Thanks for the code it is nearly working as I wanted. The range of cells is "A7:AH39" not a named range. As you mentioned the code at present leaves one row blank at the top of the range ie: row "A7", but does move all the data below up .

THKS