Pls Help - not sure what is causing problem

[abqhusker]

I have normal worksheet for class attendance, first column (A) has
student names, next column (B) has countif formula, remaining columns
have date labels and X for present and A for absent. I copied names
from webpage and pasted into excel, then used paste special using
multipy x 1 to remove hyperlinks property from names in column A.
Everything seems to be fine. I've done this in the past and never
noticed any problem. But over the past couple of day, I've come to
discover that I am unable to get the autofill/copy pointer
(crosshair)from the botton right corner of the cells in Column A (or
column B) that contain names. What I get instead is the 4-headed
arrow pointer. As I click on the bottom right corner of the cell, I
end up selecting a hidden object which I can then resize. The problem
is I can select the object and delete it but there appears to
different amount of objects layered on top each other for each cell.
I have to delete all the objects before I am able to obtain the
autofill pointer. I tried using the clear command but all is does is
clear the cell not the objects. It's hard for me to explain what the
problem is in words so I am attaching a printscreen of the problem.
Has anyone ever had this problem before, is there an easy way to fix
it, and how do I avoid this in the future? My only guess is that
there's something that happens when I remove the hyperlink formatting
from the names by using the paste special multipy x 1 trick...but that
doesn't explain why column B has the same problem though. Thanks for
your time and appreciate any help you can give.

Ed

For some reason, does not seem like first email made it through to
group. I am resending this time without attachment, hope you can
understand what I'm asking without printscreen. Thanks

Ed

 

[Pete_UK]

Another way of getting rid of all the hyperlinks once you have pasted
them into the worksheet (with them still highlighted) is to press Alt-
F11 to bring up the VBE and then Ctrl-G to enter the immediate window
and then type:

selection.hyperlinks.delete

and press Enter.

It seems as if you have a number of "shapes" in the sheet as well, so
to get rid of all of these you can enter this into the immediate
window:

For Each Sh in ActiveWorkbook.Shapes : sh.Delete : Next Sh

then press Enter.

Hope this helps.

Pete

 

[abqhusker]

Hi, Pete. Thanks for the reply. I can't press enter after entering For
Each Sh in ActiveWorkbook.Shapes : sh.Delete : Next Sh stmt in immediate
window. I get a VB error box saying "Run-time error 438: Object doesn't
support this property or method". I'm just noticing also that when I
select a cell, the name box will show A5, that's correct. But when I
try to select the autofill handle, the name box will say Picture 643.
When I expand the object it looks similar to a text box. While it's
selected, I try to type something in the formula bar and press enter and
I get a warning dialog box that says "Reference is not valid." Thanks
for taking the time to read my question and trying to help me out.

Ed

 

[Pete_UK]

I've just tested this amendment out on a file with lots of pictures:

for each pic in activesheet.shapes : pic.delete : next

and it got rid of all the pictures. Type this in the Immediate window
as before (Alt-F11, followed by Ctrl-G).

Hope this helps (this time).

Pete

 

[abqhusker]

Pete, you are a genius. That worked perfectly. I would have never
figured it out. I don't know how those picture objects got on there and
I don't know why your solution works but that's the ticket. Just got to
make sure I save this answer forever for future reference. Thanks again
for all your help. You guys are ridiculously smart with excel.

Ed

 

[Pete_UK]

Hi Ed,

thanks for the feedback. Glad it worked in the end, and I'm sorry for
the wrong syntax in the first posting (I should have tried it first!).

If it is something you might need to do several times in the future,
you could easily make it into a macro stored in your personal.xls
file, so that it's always available.

Pete