question about referencing columns 2

[marduk813]

I'm trying to reference a group of columns based on an incrementing integer variable, and I'm having some trouble selecting them. I have a column counter variable called intColCounter, and I'm using the Columns property the way VBAJedi mentions here: thread707-730362.

Columns(intColCounter + 2 & ":" & intColCounter + 10).Select

The above code keeps generating an "application-defined or object-defined error". I've used this type of referencing with the Rows property and never had a problem, but Columns doesn't seem to be working for me. In fact, if I change the above code to reference rows instead, it works fine. I'm stumped.

Any ideas?

 

[Andrzejek]

Columns do not have numbers (normally).

If you create macro by selecting columns you will have:

Columns("C:H").Select

So you may want to change your intColCounter to capture letters instead.

HTH

---- Andy

 

[SkipVought]

Hi,

Try this...

Range(Cells(1, intColCounter + 2), Cells(1, intColCounter + 10)).EntireColumn.Select

You really ought to try to program, using the Select or Activate methods as little as possible.

Skip,
[sub]
[red][/red]
[/sub]

 

[marduk813]

Skip,

Thanks, that appears to have done the trick. I've seen your posts warning people to use Select as little as possible, though I'm not sure why. (Why?)

I usually don't use it much, if at all, but I didn't know of any other way of inserting multiple columns at once without using a loop (which I thought would be worse).

Thanks again!

Jas

 

[marduk813]

I've also just realized that I never needed to select the entire columns to begin with. Selecting just a row of cells in the range of columns does the same thing when I insert. I learn something new every day...

 

[SkipVought]

Range(Cells(1, intColCounter + 2), Cells(1, intColCounter + 10)).EntireColumn.Insert Shift:=xlLeft

Select and Activate are nearly always unnecessary and can slow down a complex procedure.

I also try to avoid Insert & Delete -- instead add rows/columns at the extremity of the table and sort. Use Filter to hide unwanted rows.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

The reason for not using select / activate is because you don't need to. They set focus to certain objects which can cause problems - it is much better to just work WITH the object rather than selecting it and working with the selection

this not only speeds up code but also reduces screen flicker and the chances of the code selecting the wrong object to use and then erroring

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question