Delete Rows which have '*' in Column A 1

[TOTCOM11]

I am importing a datasheet from a statistical software package into Excel. When I do this I get an astrisk in a cell which I no longer need. I want to write a macro which will delete any rows that have an astrisk in Column A. I'm so used to writing VBA for Access, that I don't know how to write this simple code for Excel. Can anyone help me?

Thanks,
Chris

 

[PHV]

With the macrorecorder on set a filter on column A, choose * in the dropdown, delete the visible rows.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TOTCOM11]

That's great, but I would like the process to be a little more automated so I can just run the marco and have it do everything for me.

 

[PHV]

With the macrorecorder on

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TOTCOM11]

So, if I do this will it not matter how many astrisks I have in that column...it will just delete all of them no matter what?

 

[kodr]

I think the code below will get you started. (although the suggestion of using the macro recorder is allway's a good place to start.)

do while activecell.value <> ""
   if instr(1, activecell.value, "*", vbtextcompare) >1 then
      selection.entirerow.select
      selection.delete shift:=xlup
   else
      activecell.offset(1,0).select
   end if
loop

This will loop throught the sheet as long as column A has something in it. Everytime it reaches a cell in column A with an '*', it will delete the entire row.

 

[TOTCOM11]

Thanks Kodr! I really gotta learn VBA for Excel!