Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default VBA to select multiple columns

I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default VBA to select multiple columns

Hi Marvin,

Dim Rng as range
set Rng = ActiveCell.EntireColumn.Resize(, 3)

It is rarely necessary to make selections, but if you do:

ActiveCell.EntireColumn.Resize(, 3).Select

---
Regards,
Norman



"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.



  #3   Report Post  
Posted to microsoft.public.excel.programming
MDW MDW is offline
external usenet poster
 
Posts: 117
Default VBA to select multiple columns

Try Range("C:E").Select

"Marvin" wrote:

I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default VBA to select multiple columns

Hi Marvin,

I missed the cell value bit:

Sub Tester()
Dim Rng As Range
Dim i As Long

i = Range("A1").Value
Set Rng = ActiveCell.EntireColumn.Resize(, i)
Rng.Select
End Sub

---
Regards,
Norman



"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,337
Default VBA to select multiple columns

one way using numbers.
Sub selectcolumns()
'Columns("a:e").Select
Range(Cells(1, 1), Cells(1, 5)).Columns.Select
End Sub

--
Don Guillett
SalesAid Software

"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default VBA to select multiple columns

Norman-

Thanks. Perfect solution.


-----Original Message-----
Hi Marvin,

Dim Rng as range
set Rng = ActiveCell.EntireColumn.Resize(, 3)

It is rarely necessary to make selections, but if you do:

ActiveCell.EntireColumn.Resize(, 3).Select

---
Regards,
Norman



"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.



.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default VBA to select multiple columns

Hi Don,

Range(Cells(1, 1), Cells(1, 5)).Columns.Select



I think you intended:

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select

---
Regards,
Norman



"Don Guillett" wrote in message
...
one way using numbers.
Sub selectcolumns()
'Columns("a:e").Select
Range(Cells(1, 1), Cells(1, 5)).Columns.Select
End Sub

--
Don Guillett
SalesAid Software

"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.





  #8   Report Post  
Posted to microsoft.public.excel.programming
MDW MDW is offline
external usenet poster
 
Posts: 117
Default VBA to select multiple columns

Sorry, I didn't read the entire question.

I'll assume you know how many columns you want to select (and it's stored in
a variable called intNum), and you've got a range reference to the cell whose
value you want to check (called objRG).

If objRG.Value = "TheValueYouWant" Then

intStartCol = objRG.Column
intEndCol = intStartCol + intNum

For I = intStartCol To intEndCol

Columns(I).Select

Next

End If

"MDW" wrote:

Try Range("C:E").Select

"Marvin" wrote:

I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default VBA to select multiple columns

Hi Don,

Just tested. Both do the SAME and I like to keep em short


From the intermediate window:

Range(Cells(1, 1), Cells(1, 5)).Columns.Select
? Selection.address
$A$1:$E$1

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select
? Selection.Address
$A:$E

What was your test?

---
Regards,
Norman



"Don Guillett" wrote in message
...
Just tested. Both do the SAME and I like to keep em short.

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Range(Cells(1, 1), Cells(1, 5)).Columns.Select



I think you intended:

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select

---
Regards,
Norman



"Don Guillett" wrote in message
...
one way using numbers.
Sub selectcolumns()
'Columns("a:e").Select
Range(Cells(1, 1), Cells(1, 5)).Columns.Select
End Sub

--
Don Guillett
SalesAid Software

"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.










  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default VBA to select multiple columns

Not sure what you tested, but they are quite different in my tests:

Range(Cells(1, 1), Cells(1, 5)).Columns.Select
? selection.Address
$A$1:$E$1 ' 5 cells

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select
? selection.Address
$A:$E ' 5 columns


--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
Just tested. Both do the SAME and I like to keep em short.

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Range(Cells(1, 1), Cells(1, 5)).Columns.Select



I think you intended:

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select

---
Regards,
Norman



"Don Guillett" wrote in message
...
one way using numbers.
Sub selectcolumns()
'Columns("a:e").Select
Range(Cells(1, 1), Cells(1, 5)).Columns.Select
End Sub

--
Don Guillett
SalesAid Software

"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.








  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,337
Default VBA to select multiple columns

Both of you are correct and I was wrong. I was in a hurry I guess I
commented wrong when testing.
..columns 'gets a1:e1
..entirecolumn 'of course, does the whole column
so solly

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Just tested. Both do the SAME and I like to keep em short


From the intermediate window:

Range(Cells(1, 1), Cells(1, 5)).Columns.Select
? Selection.address
$A$1:$E$1

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select
? Selection.Address
$A:$E

What was your test?

---
Regards,
Norman



"Don Guillett" wrote in message
...
Just tested. Both do the SAME and I like to keep em short.

--
Don Guillett
SalesAid Software

"Norman Jones" wrote in message
...
Hi Don,

Range(Cells(1, 1), Cells(1, 5)).Columns.Select


I think you intended:

Range(Cells(1, 1), Cells(1, 5)).entireColumn.Select

---
Regards,
Norman



"Don Guillett" wrote in message
...
one way using numbers.
Sub selectcolumns()
'Columns("a:e").Select
Range(Cells(1, 1), Cells(1, 5)).Columns.Select
End Sub

--
Don Guillett
SalesAid Software

"Marvin" wrote in message
...
I can use a statement like:

rows("3:5").select

I can use a statement like:

columns(3).select

but

columns("3:5").select

fails.

My objective is to select a number of columns based on a
cell value, starting with the current column.
Is it necessary for me to calculate the column "letters"
or is there a simpler way?

Thanks.










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I cant select rows or columns cp402 Excel Discussion (Misc queries) 0 April 6th 10 08:17 PM
select multiple criteria multiple worksheets koneil Excel Worksheet Functions 1 December 12th 07 05:31 PM
Select second columns only My View Excel Discussion (Misc queries) 3 July 7th 05 11:37 PM
Select from Columns ten Excel Programming 1 August 13th 03 05:56 PM
Printing Only Select Columns Bill Martin Excel Programming 1 July 31st 03 04:56 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"