macro help please

[Silvabod]

http://s41.yousendit.com/d.aspx?id=3VBCNO99P2VB622EGB6KMP779I

This links to my 64kb worksheet, please download if you can help, post back
the upgrade link?

I'm having trouble with creating correct macro. Sheet 2 is "testing" page,
Sheet 1 is the current working worksheet.
Got so far, via "record macro" but then couldn't add in a subroutine for
"user select" - got error that I can't resolve.

I want a macro (with a button) to do the following -

Select the datablock B1 : I90 (name, numbers and "paid" columns)
USER select a name for deletion
Delete/Clear specifics of the selected name, i.e. cells B : I only
Select datablock B1 : I90
Data/Sort, "sort" col B (to restore contiguous alphasorted names)
Ask for another name for deletion
If Yes, loop to beginning
If NO, end.

Col A is sequential numbers 1 - 90 (and must remain)
therefore it cannot be "delete row" - that would also delete other
programming in later columns.
All cells in the datablock B1 : I90 have conditional formatting, which must
be retained.

Will be adding an "insert new" macro/button, but I think that's relatively
simple, just "go to" end data, add "new name/data" at end, re-sort. I'd like
the "user" facility with "add another" Y/N, will "crib" that from the
hoped-for help on "delete" macro. I hope!!

Help would be appreciated.

 

[tra]

You can use this macro:

Sub DeletePerson()

Dim myRow

If ActiveCell.Column > 9 Then 'Check if you have selected a name
MsgBox "Please select a person to delete, then run this macro
again.", , """The Admiral"" Lotto"
End
End If

myRow = ActiveCell.Row


Range("B" & myRow & ":I" & myRow).ClearContents 'Delete person

'Sort Range
Range("B1:I90").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


What you do is you click on the name of the person you want to delete,
then run the macro. If you forget to select a person, however, then the
macro won't run.

 

[Silvabod]

Thanks, Toby, much appreciated!
Pasted your code into my own macro (deleting all mine) so it is now called
DELETEPUNTER

Ran "Debug" to check, it errored on the "msgbox" line. Guessed that the
final bit should be the actual name of the worksheet, so corrected it to
"lottery1.xls". Debug now passes over, so hopefully guessed correctly.

Debug now highlights this line -

 

[tra]

I think the problem was that word wrap chopped the lines of code in
half as I sent them over, so you got those syntax errors because there
was a line break where there shouldn't be. I've edited the code to make
the lines shorter, by adding an underscore at the end of any line of
code that continues on to the line below it, so hopefully they should
stay intact.

Sub DELETEPUNTER()


Dim myRow

'Check if you have selected a name

If ActiveCell.Column > 9 Then
MsgBox "Please select a person to delete, then run " & _
"this macro again.", , "lottery1.xls"
End
End If


myRow = ActiveCell.Row


Range("B" & myRow & ":I" & myRow).ClearContents 'Delete person


'Sort Range

Range("B1:I90").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

That last section in the msgbox statement referred to what's displayed
in the title bar of the message box. It can be anything you want but if
you leave it out, it defaults to 'Microsoft Excel'

The 'Header:=xlGuess' you were referring to shouldn't really have been
on a line of its own; it was only like that because of problems with
word wrap. It's actually part of one big line of code which covers
everything between the 'Sort Range comment and the End Sub statement.
This is where the names are sorted into alphabetical order. The
'Header:=xlGuess' part tells Excel to guess whether or not you have
column headings for your data.

 

[Silvabod]

Thanks, Toby!
I played around with original code (on the dummy sheet, direct copy of
original worksheet) and worked out that the "error" bit was effectively the
"sort" routine. So, I recorded a "sort", copied it into your macro, deleted
the error line, and it worked - as you say, it was probably an inadvertent
space added via word wrap, but, certainly not obvious to a novice.
Also, created a button to run it (found a two line "how to" in on-line MS
Excel Help forum - WHY can't they just put it in Excel Help??? all that
witters on about is putting one on a toolbar!!!) .
Couple of "oddities". Somehow, the entire "macro" code got inserted into the
"names" area (cols B1:B12) on one early "run"- don't know how. Think it
might be to do with the macro's last action leaving the datablock B1 : I90
selected. Fixed it by adding a single cell "select" at the end (to a
position next to the macro button), since which, works fine.
Appreciate the help - in giving it, you taught me some things.
Next objective, to try to "loop" it at the beginning - delete a name, then
ask "is there another deletion Y/N - if Y select the name (user prompt to
choose), if N, perform the "Sort" then, Exit.
Plaese note - I was never asking for the whole sheet to be "coded" - there's
already 12 hours worth of design and formulae/conditional formatted code in
it, all of which works - it's the VBA macro code which can't be "recorded"
that I need help with.
Thanks again, and, if you can help/point me in the right direction ....
Silvabod

 

[tra]

You mentioned about how some lines of code appeared at the top of the
spreadsheet. I think this is because in cells B69:B81 on sheet 2 you
appear to have typed some lines of code. When your list was sorted,
these lines of code were sorted as well and they were moved to the top.
I have edited the code so that those cells are not included in the
sort. This seems to have solved the problem.

Prompting the user to select another name to delete at the end is a bit
more tricky. You can use the Application.InputBox function to do this,
which you appear to have used in those lines of code typed into cells
B69:B81. However, I did not have much success with this method because
the function can return either a Range or Cancel. This meant that I
kept getting 'Type Mismatch' and 'Object Expected' errors so I decided
to give up on this idea.

Instead, I included a feature that allows the user to select multiple
names in the usual way you select multiple cells by clicking and
dragging or holding down Ctrl and clicking on each person you want to
delete. The macro then loops through each cell selected, ignoring any
cells that are outside the list of people, and deletes all of the
people in the cells that are selected. The range is then sorted as
normal, but excluding the lines of code as I mentioned above.

You said how you had trouble finding how to put a button on a worksheet
in help. There is some information there which you would probably find
useful. If you open help (make sure you open it from Excel itself, not
from the Visual Basic editor) and go to the contents tab, find the
'Creating and Using Forms' section. There is some information here
about putting controls on a worksheet, including buttons, text boxes,
tick boxes etc.

The modifided code is as follows:

Sub DELETEPUNTER()


Dim x As Range
Dim a
a = 0

For Each x In Application.Selection.Cells
If x.Column < 10 Then

Range("B" & x.Row & ":I" & x.Row).ClearContents

a = a + 1

End If
Next x
If a = 0 Then
MsgBox "Please select a person to delete, then run " & _
"this macro again.", , "lottery1.xls"
End If


'Sort Range

Range("B1:I61").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


End Sub