hobbiton73
02-15-2013, 09:19 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to remove cell content, but I've come across a small issue which I'm not sure how to resolve.
Sub DelRow()
Dim RangeToClear As Range
Dim msg As VbMsgBoxResult
Sheets("Input").Protect "password", UserInterfaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
If msg = vbNo Then Exit Sub
With Selection
Application.Intersect(.Parent.Range("A:S"), .EntireRow).Interior.ColorIndex = xlNone
Application.Intersect(.Parent.Range("T:AE"), .EntireRow).Interior.ColorIndex = 42
On Error Resume Next
Set RangeToClear = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not RangeToClear Is Nothing Then
RangeToClear.ClearContents
End If
ActiveSheet.Range("A7:AG400").Sort Key1:=Range("B7"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Intersect(.Parent.Range("C:AE"), .EntireRow).Locked = True
Application.Intersect(.Parent.Range("AG:AG"), .EntireRow).Locked = True
End With
Application.EnableEvents = True
End Sub
At the moment the code removes the cell content, moves the rows up and then sorts the cells in a given column.
The problem I have is that the very last row will always show a text value of "Enter your name" which guides the user where they should start the next record but I don't want this value to be included in the sort.
I just wondered whether someone could perhaps tell me please how I may change this so this value is not included.
Many thanks and kind regards
Chris
I'm using the code below to remove cell content, but I've come across a small issue which I'm not sure how to resolve.
Sub DelRow()
Dim RangeToClear As Range
Dim msg As VbMsgBoxResult
Sheets("Input").Protect "password", UserInterfaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
If msg = vbNo Then Exit Sub
With Selection
Application.Intersect(.Parent.Range("A:S"), .EntireRow).Interior.ColorIndex = xlNone
Application.Intersect(.Parent.Range("T:AE"), .EntireRow).Interior.ColorIndex = 42
On Error Resume Next
Set RangeToClear = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not RangeToClear Is Nothing Then
RangeToClear.ClearContents
End If
ActiveSheet.Range("A7:AG400").Sort Key1:=Range("B7"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Intersect(.Parent.Range("C:AE"), .EntireRow).Locked = True
Application.Intersect(.Parent.Range("AG:AG"), .EntireRow).Locked = True
End With
Application.EnableEvents = True
End Sub
At the moment the code removes the cell content, moves the rows up and then sorts the cells in a given column.
The problem I have is that the very last row will always show a text value of "Enter your name" which guides the user where they should start the next record but I don't want this value to be included in the sort.
I just wondered whether someone could perhaps tell me please how I may change this so this value is not included.
Many thanks and kind regards
Chris