Deleting alternating cells

[Mood]

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

[klb]

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.

[Mood]

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

[NateO]

Probably something along these lines:

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 ]

[Mood]

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

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

[NateO]

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.

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 ]

[Mood]

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

[Tom Schreiner]

Maybe change:

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

to:

If myid = 0 Then Exit Sub

Tom

[NateO]

Try the following (Much like Tom Stated):

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 ]

[Mood]

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