Ignore First row

[Bec]

I am trying to delete some columns programmatically. I have been recording a
macro and then editing the code.

This is the code I am using:
Columns("E:G").Select
Range("E2").Activate
Selection.EntireColumn.Hidden = True

This works fine for the macro. When I go to rerun it, it fails. If I delete
the top merged heading row then it works. This heading gets automatically
created from another program so I can't delete it but I want my code to
ignore the top row when deleting the columns programmatically

I hope I am making myself clear. And I hope that someone can help me out.

Cheers
Bec

 

[Gord Dibben]

You can't hide just part of a column.............all or nothing


Gord Dibben MS Excel MVP

 

[Bec]

I am trying to hide the entire column. It works fine when I record the macro.
No problem and I don't have to delete the top row. But when I run the macro
it doesn't work. It deletes all my columns. After hours or fiddling I tried
deleting the top row and running the macro again and it worked. But I can't
delete the top row generally as it keeps getting regenerated anyway. So I
need a way to run the macro with ignoring the top row.

It's weird because it does work when I first record the macro so obviously
excel knows to ignore the top row then just not when it reruns the macro

 

[paulgu]

Hi Bec,

You say "but I want my code to
ignore the top row when deleting the columns programmatically". However your code hides columns it doesn't delete them. When I tried it, it didn't seem to have any problem with merged cells in the first row except that the selection wasn't limited to columns E:G - instead it selected all columns spanned by the merged cells, and hid them. Is that what you mean by "failed"?
If so and you don't want to delete the first row I suggest you just unmerge the cells in the first row. You won't be able to hide columns that contain cells merged with cells in unmerged columns.
If not, perhaps you'd better clarify what you mean.
I hope that helps,
Paul

 

[Gord Dibben]

Your code is not deleting columns, it is hiding Columns E through G

I guess I don't understand what you mean by "ignoring the top row".

When hiding or deleting columns you cannot ignore the top row or those columns.


Gord

 

[Bec]

Hi Gord,

Apologies for teh confusion I did mean hide not delete. The problem is you
can actually do it. I can do it manually all the time and it performs fine
but as soon as you go to rerun what I've just recorded it doesn't work. So it
does in fact hide the column and ignore the first row when you do it manually
but not programmatically. That's the bit that has stumped me.

Cheers
Bec