Hi. I'm stuck near the last line of code. All of the rest works great but I can't get the syntax right to delete a row of data on the line: Active sheet.rows(Sres).Delete Shift:= shiftxlUp
When I step through it I get the error "Type Mismatch
Any help will be appreciated
THANKS
Code
Sub DeleteSortedItem()
Dim Sitem
Dim RC As String
Dim SheetNum As StringShift:
Dim Rng As String
Dim rg As Range
Dim Sres
Sheets("VALUES for MEALS").Activate
Rng = Range("A65536").End(xlUp).Offset(4, 7)
SheetNum = Rng
'is the variable that holds the line number to be deleted
RC = Range("A" & SheetNum)
'is the variable that holds data in col "A"
MsgBox Range("A" & SheetNum) 'this equalled "TEST" in col "A"
ActiveSheet.Rows(SheetNum).Delete Shift:=xlShiftUp
MsgBox RC 'This also equalled "TEST" in Col "A"
'This block selects the line# to delete & saves data in variable RC _
to compare with all category sheets (ie: calories, etc.) for deletion
'Sitem = Sheets("VALUES for MEALS").Range("RC")
Sitem = RC
Set rg = Sheets("Calories").Range("A4:A600")
Sheets("Calories").Activate
Sres = rg.Find(Sitem)
If Sres = RC Then
Result = MsgBox("You are about to delete" & vbNewLine & "Meal Item..." & "*" & RC & "*" _
& vbNewLine & vbNewLine & "To Continue Click YES" & vbNewLine & "To Abort Click NO", vbYesNo + vbExclamation, _
"Delete Meal Item Box")
MsgBox Sres 'Shows up as Test
'MsgBox Sres.Address 'Shows up as A219
If Result = vbYes Then
ActiveSheet.Rows(Sres).Delete Shift:=xlShiftUp
MsgBox "You just deleted" & vbNewLine & "Meal Item...." & RC
Else
MsgBox "No Problemo" & vbNewLine & "Just click 'OK' (below)" & vbNewLine & "and Try Again"
End If
End If
End Sub
Display More