hobbiton73
02-05-2013, 09:56 AM
Hi, I wonder whether someone may be able to help me please with a urgent query.
I'm using the code below to remove cell content from a user input form. I'm using a cell 'Content Removal' method rather than a 'Delete Row' because I wish to maintain the 'Input Range'.
Sub DelRow()
Dim msg
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
Selection.SpecialCells(xlCellTypeConstants).ClearContents
Application.Intersect(.Parent.Range("C:AE"), .EntireRow).Locked = True
Application.Intersect(.Parent.Range("AG:AG"), .EntireRow).Locked = True
End With
Application.EnableEvents = True
With Range("A7:AG400" & Cells(Rows.Count, "A").End(xlUp).Row)
.Sort Key1:=Range("B7"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The code works fine except in one scenario.
'User A' enters data into rows 1-4, saves, and closes the file.
'User B' then enters data into rows 5-8, saves and closes the file.
'User A' then opens the file again and filters the spreadsheet on their name. They then start to enter another row of data. However because they have applied the filter this record appears on row 392.'
'User A' then removes the filter where they are faced with hundreds of empty rows between the last record created by 'User B' and the the new record they have just created.The problem I have is that when they highlight the empty rows and action the 'Delete Row' macro shown above, they receive a 'Error 400' error message, thus rendering the file useless.
I've been trying to work this out all day at work without any success.
I just wondered whether someone could possibly look at this please and help and offer some help as to how I may over come thius.
Many thanks and the kindest regards
I'm using the code below to remove cell content from a user input form. I'm using a cell 'Content Removal' method rather than a 'Delete Row' because I wish to maintain the 'Input Range'.
Sub DelRow()
Dim msg
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
Selection.SpecialCells(xlCellTypeConstants).ClearContents
Application.Intersect(.Parent.Range("C:AE"), .EntireRow).Locked = True
Application.Intersect(.Parent.Range("AG:AG"), .EntireRow).Locked = True
End With
Application.EnableEvents = True
With Range("A7:AG400" & Cells(Rows.Count, "A").End(xlUp).Row)
.Sort Key1:=Range("B7"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
The code works fine except in one scenario.
'User A' enters data into rows 1-4, saves, and closes the file.
'User B' then enters data into rows 5-8, saves and closes the file.
'User A' then opens the file again and filters the spreadsheet on their name. They then start to enter another row of data. However because they have applied the filter this record appears on row 392.'
'User A' then removes the filter where they are faced with hundreds of empty rows between the last record created by 'User B' and the the new record they have just created.The problem I have is that when they highlight the empty rows and action the 'Delete Row' macro shown above, they receive a 'Error 400' error message, thus rendering the file useless.
I've been trying to work this out all day at work without any success.
I just wondered whether someone could possibly look at this please and help and offer some help as to how I may over come thius.
Many thanks and the kindest regards