View Full Version : clearcontents vs delete
av8tordude
02-24-2011, 08:38 AM
how can I change this code to clear the contents in a cell and sort the column instead of deleting the cell?
rResult.Delete Shift:=xlUp
Bob Phillips
02-24-2011, 09:44 AM
rResult.ClearContents
av8tordude
02-24-2011, 09:50 AM
XLD...
I tried that already. It says Wrong number of argument or invalid property assignment. I've used clearcontents before, but I'm wondering if it has anything to do with Shift:=xlUp
IBihy
02-24-2011, 10:56 AM
Hello,
yes, it has something to do with Shift:=xlUp, which you can only use in the delete method, and not in the ClearContents method.
For your understanding,
the Delete method actually removes (kills) the rows from the WB They are goners after delete. The parameter Shift:=xlUp signifies how the "gap" of the deleted rows is to be closed, here by shifting the below rows up.
the ClearContents method just makes the cells empty, the rows themselves are still there.
Regards,
Isabella
Bob Phillips
02-24-2011, 11:54 AM
XLD...
I tried that already. It says Wrong number of argument or invalid property assignment. I've used clearcontents before, but I'm wondering if it has anything to do with Shift:=xlUp
There is no ShiftUp in there so it cannot be anything to do with that.
How is rResult defined?
av8tordude
02-24-2011, 11:56 AM
Dim iExpCount As Integer
Dim sAnswer As String
Dim rRange As Range
Dim rResult As Range
Dim rSearch As Range
Set rRange = Range("Expense").Columns(4)
iExpCount = WorksheetFunction.CountIf(rRange, cboDesc.Value)
If MsgBox("Delete " & cboDesc & " from the database?", vbYesNo + vbQuestion, "PerDiem Travler") = vbYes Then
'delete from Category
Set rSearch = Sheets("Calculator").Range("AP10:AT100").SpecialCells(xlCellTypeConstants)
Set rResult = rSearch.Find(cboDesc.Value, rSearch.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rResult Is Nothing Then
rResult.Delete Shift:=xlUp
Set rResult = Nothing
Else
MsgBox cboDesc.Value & " could not be found.", vbInformation, "PerDiem Traveler"
End If
MsgBox "This item has been deleted from the database.", vbInformation, "PerDiem Traveler"
Del5.Visible = False
NewExpense
Frame15.SetFocus
End If
Bob Phillips
02-24-2011, 12:18 PM
SO did you try
Dim iExpCount As Integer
Dim sAnswer As String
Dim rRange As Range
Dim rResult As Range
Dim rSearch As Range
Set rRange = Range("Expense").Columns(4)
iExpCount = WorksheetFunction.CountIf(rRange, cboDesc.Value)
If MsgBox("Delete " & cboDesc & " from the database?", vbYesNo + vbQuestion, "PerDiem Travler") = vbYes Then
'delete from Category
Set rSearch = Sheets("Calculator").Range("AP10:AT100").SpecialCells(xlCellTypeConstants)
Set rResult = rSearch.Find(cboDesc.Value, rSearch.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rResult Is Nothing Then
rResult.ClearContents
Set rResult = Nothing
Else
MsgBox cboDesc.Value & " could not be found.", vbInformation, "PerDiem Traveler"
End If
MsgBox "This item has been deleted from the database.", vbInformation, "PerDiem Traveler"
Del5.Visible = False
NewExpense
Frame15.SetFocus
End If
av8tordude
02-24-2011, 08:54 PM
XLD,
I know how to use .ClearContents. But I also need to be able to sort only the affected column in a 6-column table when the item is being deleted from that column.
Bob Phillips
02-25-2011, 03:05 AM
XLD,
I know how to use .ClearContents. But I also need to be able to sort only the affected column in a 6-column table when the item is being deleted from that column.
So what has ShiftUp got to do with anything?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.