Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Deleting alternating cells

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Manitoba, Canada
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have searched the board without any luck. Iíve come close, but no cigars.

    I have a question about whether I can automatically delete certain cells all in the same row, but not in adjacent columns. All based on an answer from a popup box, or something similar??


    First, I would like to press a button to start the macro,

    Then ask the user for the id #, or cancel the macro,

    Find the row with the id# in column E, (the sheet has up to 355 rows and is dynamic),

    Then go to the left of the id# cell (same row) and start to delete the cell in column D (has a checkmark, formatted as Marlett font),

    Then delete cell in column E (id#),

    Delete cells in Columns F and G (front and back names),

    Skip the cell in column H (formulas),

    Then delete the next two cells in columns I and J (both dates),

    Skip the last two columns K and L, (the deleting basically ends in column J),

    Then a message box pops up either confirming the deletions, or if the user canceled.


    Hope Iíve made myself clear, and much thanks for all the fantastic help on this board.

    Peter

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    quick question -

    Do you mean "delete the cells" or "delete the contents of the cells"?

    Deleting cells will cause adjacent cells to shift.

    Deleting the contents leaves the structure of your workbook intact.

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Manitoba, Canada
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 12:22, klb wrote:
    quick question -

    Do you mean "delete the cells" or "delete the contents of the cells"?

    Deleting cells will cause adjacent cells to shift.

    Deleting the contents leaves the structure of your workbook intact.
    "Deleting the contents" would be correct. I would like the structure preserved for future use.

    Peter

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Probably something along these lines:

    Code:
    Sub deller()
    Dim myid As Integer, mrw2 As Long, mycl As Range
    On Error GoTo 1
    myid = Application.InputBox("Enter your id #", Type:=1)
    Set mycl = Columns("e").Find(What:=myid)
    mrw2 = mycl.Row
    Range("d" & mrw2 & ":g" & mrw2).ClearContents
    Range("i" & mrw2 & ":j" & mrw2).ClearContents
    MsgBox "Delete Confirmation"
    Exit Sub
    1: MsgBox "Action Cancelled or ID not Found"
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-05-06 12:54 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Manitoba, Canada
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 12:33, NateO wrote:
    Probably something along these lines:

    Code:
    Sub deller()
    Dim myid As Integer, mrw2 As Long, mycl As Range
    On Error GoTo 1
    myid = Application.InputBox("Enter your id #", Type:=1)
    Set mycl = Columns("e").Find(What:=myid)
    mrw2 = mycl.Row
    Range("d" & mrw2 & ":g" & mrw2).ClearContents
    Range("i" & mrw2 & ":j" & mrw2).ClearContents
    MsgBox "Delete Confirmation"
    Exit Sub
    1: MsgBox "Action Cancelled or ID not Found"
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-05-06 12:54 ]
    Boy, did that do the trick. Thank you very much... Coffee's on me.

    PS.
    I'm trying to figure it all out, but I don't quite understand it. Maybe time or experience will help.

    Much gratitude, Peter

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Peter, you're welcome. Coffee sounds great, and being in Minneapolis, I'm not too far off. It may help if I include notes, I added them in the code below (note is above pertinent line). Also, in previous code, I did two deletes, one for d:g, and one for i:j. In the code below, I delete both sections [via union] in one procedural line of code.

    Code:
    Sub deller2()
    Dim myid As Integer, mrw2 As Long, mycl As Range
    'error trapper used latter, 1 is line identifier
    On Error GoTo 1
    'inputbox with validation, input must be numeric
    myid = Application.InputBox("Enter your id #", Type:=1)
    'look in column E for 'myid' and set this as range
    'if user cancels, or XL can't find it, line above will cause an error and Xl goes to 1:
    Set mycl = Columns("e").Find(What:=myid)
    'grab the row number of the found cell
    mrw2 = mycl.Row
    'clear two sections
    Union(Range("d" & mrw2 & ":g" & mrw2), _
    Range("i" & mrw2 & ":j" & mrw2)).ClearContents
    MsgBox "Delete Confirmation" 'made it this far w/ no errors, tell end-user
    Exit Sub 'exit sub so not to show message below
    1: MsgBox "Action Cancelled or ID not Found" ' 'mycel' could not be set, here's the error
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-06 15:46 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Manitoba, Canada
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Code:
    myid = Application.InputBox("Enter your id #", Type:=1)
    
    'if user cancels, or XL can't find it, line above will cause an error and Xl goes to 1:
    Set mycl = Columns("e").Find(What:=myid)
    
    
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-06 15:46 ]
    Hi Nate

    Sorry for the delay in responding, but my job seems to be getting between me and XL.

    I've encountered a slight problem when I press the cancel button. The macro will go and delete a row and then confirm the deletion. It doesn't cancel the macro.

    I tried to place this line of code after myid:

    myid = Application.InputBox...
    If CStr(myid) = "False" Then Exit Sub

    to try and cancel the dialog box. No luck. I seem to be stumped, again.

    Off to bed I go, and thanks
    Peter



  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe change:

    If CStr(myid) = "False" Then Exit Sub

    to:

    If myid = 0 Then Exit Sub

    Tom




  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following (Much like Tom Stated):

    Code:
    Sub deller2()
    Dim myid As Integer, mrw2 As Long, mycl As Range
    'error trapper used latter, 1 is line identifier
    'On Error GoTo 1
    'inputbox with validation, input must be numeric
    myid = Application.InputBox("Enter your id #", Type:=1)
    If myid = Empty Then
    MsgBox "Action Cancelled"
    Exit Sub
    End If
    'look in column E for 'myid' and set this as range
    'if user cancels, or XL can't find it, line above will cause an error and Xl goes to 1:
    Set mycl = Columns("e").Find(What:=myid)
    'grab the row number of the found cell
    mrw2 = mycl.Row
    'clear two sections
    Union(Range("d" & mrw2 & ":g" & mrw2), _
    Range("i" & mrw2 & ":j" & mrw2)).ClearContents
    MsgBox "Delete Confirmation" 'made it this far w/ no errors, tell end-user
    Exit Sub 'exit sub so not to show message below
    1: MsgBox "ID not Found" ' 'mycel' could not be set, here's the error
    End Sub
    I was overly focused on the range test as cancellation, but we can use the inputbox as well.

    Hope this helps.
    ___________________________________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-07 09:28 ]

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Manitoba, Canada
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to both TsTom and NateO. Both of the ideas were a complete success.

    I'm one happy coffee drinker. I'll be buying the next round.

    Peter

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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