Macro to Delete Row based upon User Input Box value

Hi Guys,

I'm looking for a macro that will delete a row or multiple rows in a sheet based upon what a user puts into an Input Box.

For example, if they put 8 into the Input Box, I would want row 8 deleted.

It seems like its quite a simple bit of code, but I can't quite figure it out!

Any help would be greatly appreciated.

Cheers,
Mike

Please Login or Register  to view this content.

Perfect, thanks yudlugar!

Is there something that could be added that would allow for multiple rows to be deleted? So if the user entered something like 8, 14?

Cheers,
Mike

You can select multiple rows by typing "8:14" but if you only want 8 and and 14 you would have to do something like:

Please Login or Register  to view this content.

Assumes each row number is comma separated

Excellent, that's what I wanted, as it could be specific rows they would delete, rather than a whole range of rows.

Sorry, one final thing, is it possible to make some rows 'off limits' to delete? So say I didn't want rows 11 and 12 deleted, could I explicitly keep these off limits? And with the addition and deletion of rows, I'm assuming an offset would have to be used?

Cheers,
Mike

Yeah sorry forgot about the offset, normal method would be to work from the bottom row and loop upwards, to keep rows off limits you could do something like:

Please Login or Register  to view this content.

I'm not exactly sure the best way to loop backwards here. Either put the array in reverse order using some sort of sort function (should be some available if you google it) or do something like:

Please Login or Register  to view this content.

Think I'd probably want to use something like the first bit of code you put there, specifying which rows now to delete, as I'm not sure I understand all of the code you've used in the second bit! (bit of a VBA newcomer).

The rows I wouldn't want to delete could and will change with people adding in and deleting rows, so is there like an offset/active cell type method of keeping them separate?

You could use a named range?
then:
if not r = Range("rangename").row then rows(r).delete

Only problem with a named range is that I've made a macro that will add a new sheet with the same formatting and macros etc for new sites when the user requests. So the name ranges won't translate across all of the sheets.

Get the macro that adds the new sheet to add the named range too.

Any idea where I could slot that in?

This is the code for the new sheet:

Please Login or Register  to view this content.

If you do it in that way the names will be copied across automatically.

That's what I thought also, but it seems to only refer to the "Copy Sheet" rows 11 and 12, rather than the active sheet rows 11 and 12.

I just double checked and for me it returns the range of that name on the active worksheet. You could try adding a sheet reference before it:
Activesheet.Range("namedrange")

Am I assigning the Named Range in the right way?

I.e. I'm highlighting the rows I want to keep, and then clicking "Define Name" in the Formulas tab.

Is there a way to assign the range within the macro?

Well you probably only need to assign the name to one cell in each row but yes. Make sure you are changing the code to the name you define. To define a name with vba:

ActiveSheet.Names.Add Name:="Test", RefersTo:=Range("A10")

Ok, so I've got it to assign the named range to the different spreadsheets now, but I'm getting an error when it tries to delete the rows.

This is the code:

Please Login or Register  to view this content.

And this is the error, which the debugger says is happening on red line above:

"Run-time error '1004':

Method 'Range' of object '_Global' failed"