Excel macro and formula

wwheaton

New Member
Joined
Apr 15, 2002
Messages
12
In excel 97 I need to be able to delete a user selected row when a specified cell equals or exceeds a certain value
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try the following and post with problems:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DltRow As Integer
On Error Resume Next
If Target.Value > Range("A1").Value Then
DltRow = InputBox("Type Row Number to Delete")
Rows(DltRow).Delete
End If
End Sub

Put this code in the module for your worksheet. Target is the cell that you are comparing to Cell A1. Edit as needed.
 
Upvote 0
Thanks Al, here is where i show my ignorance. I cannot get excel to see the macro to run it. I went into VB editor and added the code as shown but how do i run it?
 
Upvote 0
This is not a macro that you run. It is an event procedure that will run, whenever your worksheet is changed. I am not sure if it is what you want. But to test it, follow the following procedure:

1. In excel hit, Alt+F11 to go into VBA
2. Look to the left in the project explorer and double click your worksheet where you want the fun to happen.
3. Look to the right for the big blank space and paste in the code.
4. Close VBA and try entering in numbers.
 
Upvote 0
Al, that exactly what i want it to do. I'm sorry but Assume i want to compare cell b1 to a1 and have the event happen when b1 is equal or greater than a1. I'm not sure where to edit it to make this happen. Again I'm not really used to working in VB and with other than simple ( really simple) code. Thanks for your patience.
 
Upvote 0
Try the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DltRow As Integer
On Error Resume Next
If Target.Address = "$B$1" And Target.Value >= Range("A1").Value Then
DltRow = InputBox("Type Row Number to Delete")
Rows(DltRow).Delete
End If
End Sub

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-04-16 10:23
 
Upvote 0
Thanks again and sorry to keep bugging you. Entered the code as shown - typed it in by hand. No errors. Went to cell a1 and entered a number(5) and then went to b1 and entered a larger number(10) and nothing happened. What am i doing wrong? Can you email me a spread sheet with an example? Email address is wayne.wheaton@eds.com

Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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