Anyone help with a simple excel macro

[Efour]

Im editing a mailing list, ive exported it from outlook to excel.

basically i have a list of people i want taken out on A4 paper, i dont have the original .doc

all i am doing is searching for names then deleteing the entire row

can anyone help me with a 1 key macro type thing that will hightlight the entire row where the cell containing the name is, delete it and shift the cell up one.

I dont think its hard but Ive completely forgotten everything but some of the most basic stuff.

thanks

 

[Efour]

oh the row width is upto CJ i think you need to know that

I appreciate this ill buy you a beer one day.

 

[A[L]C]

send me the file

[email protected]

I'll see what I can do

 

[Efour]

ah, thanks for the offer call me a sad case but its the companies client database and as much as i know theres no way on earth that you would copy it , its all this frigging company is worth

ehehe.

any other way of doing this or i'll do a qwik crash course in excel over dinner.

Sorry if i come across as a cowardly jobsworth idiot .

 

[Gaverick]

This should work, but backup the sheet first just incase.

Create a new row on the bottom of your current list, just fill it with random data

Go to Tools > Macro > Record New Macro

Type in the macro name (whatever you want) and assign a shortcut then press ok
Highlight the entire new row you made earlier and right click on the row letter and select delete.

Stop the macro recording

Go to Tools > Macro > Macros and select the macro you just made

Select edit

Find the line which looks like this

Rows("1:1").Select

Delete that line from the file

Close the VB editor

Highlight the row you want to delete, then press the shortcut you assigned earlier

This works for me, using Office XP.

 

[Scottland]

<snip>

I'm not so sure that will work, as he's wanting to search for specific data/names and have it delete them automatically.

I don't know what rows/columns you have in the spreadsheet, but you can modify this to your own ends.
This is assuming Column A has a list of names, and in cell B2 you type in the name you're searching for (exact match).

Sub whatever()

Dim iloop1 as Integer
Dim sName as String

sName = Range("B2")
iloop1 = 2 'Assumes Row 1 is field name, row 2 is start of the data

Do Until Range("A" & iloop1) = ""
If Range("A" & iloop1) = sName Then
Rows(iloop1).Delete
iloop1 - 1
End If
iloop1 = iloop1 + 1
Loop

End Sub

I've not tested that, but it should work. Backup before you test it or at least run it on some test data.

If you have gaps in the data you can change the Do statement to "Do Until iloop1 = <last row of data>"
Also, if what you're searching for is in different formats (i.e. Alan Smith could be Mr A Smith as well as Mr Alan Smith or Smith,Alan etc) then it won't work as it'll only delete the ones it matches exactly to what you're searching for.

There are various ways you could get around this, but it should get you started.

HTH

 

[Gaverick]

ah i thought he meant he was just searching by eye

 

[Scottland]

ah i thought he meant he was just searching by eye

I've just re read it, and he may have meant that

If you've got a long list of names, and they can all be exactly matched to the excel file; then you can expand it to a search for a list of names and do it all in 1 go

 

[Efour]

thanks chaps, yeah i didnt word the original post that well

Im searching using the "find" tool thing for an email address then deleteing the entire rows worth of data. Its an export of the contacts address book from Outlook 2003. theres about 20 or so coloumns worth of junk im not interested that im getting rid of too.

Most of my time is taken up by highlighting the entire row right clicking delete and shift cells up.

so wanted a shortcut i could press that after i have found the offending row that simply selects all of it, deletes it and shifts the cells up one.

ill try this in the morning it sounds promosing

appreciate the help !

 

[dan^uk]

This short macro will delete the row where the selected cell is located (or the first cell of a selection):

Sub DeleteRow()
    Dim CellAddress As String
    CellAddress = ActiveCell.Address
    Rows(ActiveCell.Row).Select
    Selection.Delete Shift:=xlUp
    Range(CellAddress).Select
End Sub

After you have added the macro, go to the macro list window, select the macro, click the options button and then assign a shortcut to it (such as Ctrl+D).

 

[Scottland]

thanks chaps, yeah i didnt word the original post that well

Im searching using the "find" tool thing for an email address then deleteing the entire rows worth of data. Its an export of the contacts address book from Outlook 2003. theres about 20 or so coloumns worth of junk im not interested that im getting rid of too.

Most of my time is taken up by highlighting the entire row right clicking delete and shift cells up.

so wanted a shortcut i could press that after i have found the offending row that simply selects all of it, deletes it and shifts the cells up one.

ill try this in the morning it sounds promosing

appreciate the help !

You can actually right click on the number of the row, and select delete. It's much quicker