PDA

View Full Version : Remove Last Row Value From Sort



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

patel
02-15-2013, 11:12 AM
attach a sample file with comments

hobbiton73
02-15-2013, 11:48 AM
Hi @patel, thank you for taking the time to reply to my post.

Please find attached a test file for you to use.

If you could please enter:

'Chris' in B7
'Bob' in B8 and,
'Helen' in BN9

Then please select row 8 and press the 'Delete' button. You will then be able to view the scenario and problem which my post highlights.

Many thanks and kind regards

Chris

patel
02-16-2013, 02:54 AM
Sub DelRow()
Dim RangeToClear As Range
Dim msg As VbMsgBoxResult, lr As Long

Sheets("Input").Protect "handsoff", 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 ' or previously defined error handler
If Not RangeToClear Is Nothing Then
RangeToClear.ClearContents
End If
lr = Cells(Rows.Count, "B").End(xlUp).Row - 1
ActiveSheet.Range("A7:AG" & lr).Sort Key1:=Range("B7"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows(lr).Delete
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

hobbiton73
02-16-2013, 06:29 AM
Hi @patel, thank you so very much for your help with this. There is just one minor issue though.

The deletion works well if a value other "Enter your name" is present in the preceeding row, but if I delete all but the row containing "Enter your name", the rows don't move up, filling the blank rows.

Could you tell me please, have you any ideas.

Many thanks and kind regards

Chris