+ Reply to Thread
Results 1 to 6 of 6

Delete a row based on value of 2 combo boxes (2 columns)

Hybrid View

  1. #1
    Registered User
    Join Date
    07-20-2017
    Location
    San Diego, CA
    MS-Off Ver
    2010
    Posts
    20

    Delete a row based on value of 2 combo boxes (2 columns)

    Good afternoon,
    I am creating an excel program that tracks vacation time. My issue is if you were to cancel vacation time and I want to delete that row.

    I have a combo box with an active range of names that updates as new names show up.

    i have another box (I have tried a listbox and combo box) that will display the start date of all vacation times that person has put in for.

    I am using this code:

    Dim iLastRow As Long
    Dim i As Long

    iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
    For i = iLastRow To 1 Step -1
    If Cells(i, "a").Value = LEAVEDELETE.SAILORpick.Value And Cells(i, "b").Value = LEAVEDELETE.LEAVE.Value Then
    Rows(i).DELETE
    End If
    Next i

    where leavedelete is my userform, sailorpick is the first combo box with names, and leave is the 2nd box with a list of start dates.

    I have tried this code and it worked:
    If Cells(i, "a").Value = LEAVEDELETE.SAILORpick.Value And Cells(i, "b").Value = "(manually puting in date here)" Then

    for some reason, the value in the listbox/combobox is not the same value as the cell with the actual date even though they read the same and in the same format.

    Any suggestions??

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Delete a row based on value of 2 combo boxes (2 columns)

    Text base controls (Like textbox, listbox, combobox) are just that, Text.

    Date in a cell is a number.

    Maybe
    If Cells(i, "a").Value = LEAVEDELETE.SAILORpick.Text
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    07-20-2017
    Location
    San Diego, CA
    MS-Off Ver
    2010
    Posts
    20

    Re: Delete a row based on value of 2 combo boxes (2 columns)

    Thank you so much! I had to wait until weekend was over to attempt it and it worked. The moment I was reading that a date was a number, I did a face palm....I've had to utilize that before on other things and I should have known.

  4. #4
    Registered User
    Join Date
    07-20-2017
    Location
    San Diego, CA
    MS-Off Ver
    2010
    Posts
    20

    Re: Delete a row based on value of 2 combo boxes (2 columns)

    Oh, I didn't realize the reply button doesn't reply to just one person, but for the whole thread. First time posting an issue and cant find a delete button for my previous replies. Tinbendr, your code worked perfectly. Thank you.

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,304

    Re: Delete a row based on value of 2 combo boxes (2 columns)

    This should do it.

    If Cells(i, "a").Value = SAILORpick.Value And Cells(i, "b").Value = cdbl(LEAVE.Value) Then
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Registered User
    Join Date
    07-20-2017
    Location
    San Diego, CA
    MS-Off Ver
    2010
    Posts
    20

    Re: Delete a row based on value of 2 combo boxes (2 columns)

    I attempted this code, but I was getting a missmatch error. the previouse guy's code worked though so thank you for your time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Populate combo with values from one column and text boxes from other columns
    By Tea_4_2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-15-2016, 07:33 PM
  2. Replies: 4
    Last Post: 05-19-2015, 11:53 PM
  3. Filling Combo boxes based on the value of one combo box
    By KSChan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2014, 12:48 AM
  4. Formula based on user input from combo boxes
    By GeorgeHSmith in forum Excel General
    Replies: 3
    Last Post: 07-18-2011, 05:55 PM
  5. Calculation based on multiple Combo Boxes
    By emueller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2010, 10:01 AM
  6. Hooking Up VBA With Sheet Based Combo Boxes?
    By SamuelT in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-21-2009, 05:09 AM
  7. Charts based on date with combo boxes
    By ipodman in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-27-2007, 03:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1