Deleting alternating cells

Mood

New Member
Joined
Mar 12, 2002
Messages
22
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by nateo on 2002-05-06 12:54
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

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
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-06 15:46
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

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
 
Upvote 0
Maybe change:

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

to:

If myid = 0 Then Exit Sub

Tom
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-07 09:28
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top