BENSON
06-24-2009, 04:51 AM
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
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