Delete row of data on worksheet

  • 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

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    You could test following


    Code
    1. ActiveSheet.Rows(Sres & ":" & Sres).Delete Shift:=xlShiftUp

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim. I found an error in the code above the line I was having problem with, so I corrected the code accordingly, but I'm still getting the error message: "Type Mismatch" on the same line of code. Here s the amended code:

    Thanks a ton

    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    Declaring all your variables As Variant does not help the debugging process ...


    Could you please check the different types in following macro :


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hey

    I changed the variables to your suggestion and I get Type mismatch on each of them designated as 'Long', when I step through the program

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Re,


    Sadly ... without your file ... quite difficult to double-guess the type you should have assigned to each variable ...:cursing:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Yeah, I get it. I don't know how you guys do any of the help, with the limited information you're given. (especially from me, ?( I guess).

    As you can tell my nemesis it assigning types to my variables. That's why I let the programs assign as variants. I've read that it's not a good way to go, but should work anyway. I've tried to learn the types, but it seems like most of the time the assignment faults out, somewhere down the line. I just can't seem to grasp the logic in it.

    I don't know if this will help but here is more information on my variables...

    I very much appreciate any help that you're giving me.


    Rng finds the value in sheet VALUES for MEALS in a specific cell that houses the line # to delete

    SheetNum is the variable where Rng is stored

    RC finds the cell location A & SheetNum where text is stored

    RCa is the variable where RC is stored

    Sitem = RCa

    rg sets the range to search text and match for similarity to RCa

    Sres is assigned to the .Find(Sitem) command

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    In my humble opinion, you are truly over-complicating your life ...


    1. The naming of your variables creates confusion whereas it is the names can easily be used to clarify the matter


    2. Do you actually have only 3 ranges : Rng, rg and RC ...???


    3. Why do you need to create variables ... as you say ... to store variables ...??? by definition a variable holds ...a ' variable '


    4. To make your life easier, just identify precisely the Ranges ... since they do require to be defined by Set ...


    5. By the way ... you forgot one Variable : Result


    6. Thanks for reviewing my guesses shown below ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim

    Honestly, I tried what you suggested and due to my experience, (or lack there of), I've screwed it up, to beyond recognition...LOL. I feel that this should be soooo easy, but I can't seem to accomplish this task. This sub is one of the last of my problems, that I've muddled through over the last year, to build this program. I can finally see daylight! Another being a problem with my CDO email that 'Uncle Stringer" & 'DaveExcel' is helping me with. (I believe that you were my mentor & provided that code.)


    I've attached a copy, which has just the 2 sheets that are the focus of my problem.JimmyB Trial.xlsm


    What I'm trying to do is this:


    I have a master sheet -"VALUES for MEALS" where row of meal item & categories go.

    The user is allowed to add meal rows into the master sheet

    It is then automatically sorted into the category sheets, ie: Calories, Total fat, etc..

    That's cool..I got that down very well.


    Need to make it possible to remove unwanted items. which I've uploaded into it,

    by placing the line # in the box provided.

    I will create a 'form control' button to activate the sub.

    The sub needs to identify the line number IE: 220

    then go to the line #... then check column "A" and store the text IE: "Test"

    Then delete the row IE: 220 where the text "Test" is located


    Then go to the "Calories" sheet

    Search column "A" for the text "Test"

    and delete that row


    That's it. Simple right?

    If you can help me with the syntax I can copy and adjust it to all of the other categories

    IE: Cholesterol, Sodium, etc...


    Thanks Again Carim...very much appreciate it

    Oh yeah,

    The sub that I worked on is trashed...best to pull up the most recent that I sent...Sorry

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Jimmy,


    Have this strange feeling you are truly over-complicating your life ...?(


    Will review your comments to try to decode what obstacles you are facing ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re,


    By the way ... forgot to mention that ... quite obviously ... you can delete a given item ( in Sheet Calories ) only if it is present ...


    For example, within Sheet1 ... rows 130,167 and 194 cannot be found in Sheet 2 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Yeah Carim. All of the sheets are identical and driven by the Values for Meals sheet. I must have deleted a few while trying different things. I'll double check them before i finalize.

    The easy solution cannot work. The main sheet is what populates the Meals Data sheet. When the user picks a food item, it automatically goes to a daily consumption list, on a different sheet. It then calculates the total amount of calories, transfat, sodium etc., transfers the data to a table which in turn creates charts. Worked hard to get that going and don't want to change it. That is why I want to use the row numbers.


    I didn't mean to send you all of the code, for the complete workbook...as rudimentary, (self taught), as it is, It's a secret...LOL. Planning to go public in the spring. I struggled all year to develop it...Hope you're as honest as you seem. LOL. I'd hate to put it out there and get sued because someone else claimed it.

    Sooooo back to the drawing board? Appreciate it

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Jimmy,


    Can you adapt the Event macro to your actual situation ... or do you need a hand ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim

    I mentioned, (in error), on my last thread, that I couldn't use the double click method that you suggested, but I was wrong. I have my original event setup, so it would not activate unless I activated it, using the (meal item selector) form control. Therefore your easy method works perfectly. I just added it below the original event and tweaked the sheet #'s to match mine. WORKS GREAT! and I love it...so easy for the user.


    The only issue I now have is that it must preform the same calculations on the next 8 sheets. So I need it to search and delete accordingly on sheets 13 through 21 (Inclusive). (VALUES for MEALS being Sheet 12). I was going to copy the calculation and paste another 8 times and adjust. I'm sure there's a better way to incorporate it into your code. Can you help me with that?

    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Jimmy,


    Glad to hear you are approaching your solution ...;)


    Below is the modification you asked for


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • BTW I don't think that I did send you all of the subs by accident. I must have had my program up & it picked them up from that.

    Also, if you are helping me on the request from the last thread, can you also insert something. so that if an item match is not found on any of the sheets, it doesn't give an error? It just continues to the next sheet

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Sorry Carim

    I didn't see your response before I sent the last thread.

    I had to change line 11 from the variable "i" to the actual sheet name. Shouldn't it be Sheet1 as this is the sheet that is what we are comparing from

    When I run the code I get an error: Object required on line 9

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Odd that it worked great until we amended the code

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    You must make sure sheets indexes are correct ... bear in mind I do not know your workbook's structure ....


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)