Selecting columns with column numbers not letters

[Chrisso]

Hi All

How can I select more than one column of a Worksheet object if I have
the columns as numbers(long) and not characters?

That is, in my example, simplified code I have:

Dim lColStart As Long, lColEnd As Long
lColStart = 1
lColEnd = 3

I want to select all columns from lColStart to lColEnd on the
ActiveSheet. It seems that VB wants:

ActiveSheet.Columns("A:C")

but I want to use my longs only - I dont want to have to convert them
to characters with a helper sub.

I must be missing something - how can I use my longs to select the
columns?

Thanks in advance for any ideas.
Chrisso

 

[Guest]

Hi,

This loops through all columns in the used range but you could easily set
the start and end columns with variables or other fixed values.

Sub marine()
LastCol =
ActiveSheet.UsedRange.Column(ActiveSheet.UsedRange.Columns.Count).Column
Stop
For c = 1 To LastCol
Columns(c).Select
'do something
Next c
End Sub


Mike

 

[Peter T]

Hi again,

One way -

ActiveSheet.Columns(3).Resize(, 3)

Regards,
Peter T

 

[Chrisso]

Thanks Mike & Peter for your code examples.

Can I conclude then that it is not possible to do something nice and
simple like:

ActiveSheet.Columns("1:3").Select

???

I will use the .Resize property(?) but VB does not make it very
readable!

Chrisso

 

[Peter T]

Can I conclude then that it is not possible to do something nice and

simple like:

ActiveSheet.Columns("1:3").Select
Corrrect

I will use the .Resize property(?) but VB does not make it very
readable!

What's unreadable about -

ActiveSheet.Columns(firstCol).Resize(,qtyCols)
or
..Resize(,lastCol-firstCol+1)

Regards,
Peter T