PDA

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?