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