Looping Macro choosing Columns

whose2know · May 2nd, 2002, 03:40 PM

I have built many macros where I set a loop up to select rows. Since rows are numeric, the statement > Range("A" & Start & ":AQ" & Start).Select < works where Start is the loop counter.

Today I ran into the need to select columns through a loop. I was trying to find how to do it using R1C1 commands, but was unable to. I know about the offset command, but that won't work in my case.

I'd appreciate any help anyone can provide.

brettvba · May 2nd, 2002, 03:44 PM

what are you actually trying to do?

whose2know · May 2nd, 2002, 03:48 PM

I'm trying to take the data in columns B to AE, copy it to another sheet (one column at at a time), sort it, delete blank rows, sort it back to original order, and then copy to a new sheet. I want to do a loop where I can select a specific column during each loop.

zacemmel · May 2nd, 2002, 03:50 PM

It is much easier if you use cells() instead of range(). Cells(row number, column number).

whose2know · May 2nd, 2002, 03:54 PM

I've never used cells() before only range().select

I'll look up cells in the help files.

Any pointers you can give me in using cells()? Is a valid statement cells(r1c1).select?

whose2know · May 2nd, 2002, 03:59 PM

how do I select B1:B44 using a cells statement? I have only been successful with selecting B1 or B44 -> cells(2,1).select

RET79 · May 2nd, 2002, 04:03 PM

Range(Cells(1,2),Cells(44,2)).select

[ This Message was edited by: RET79 on 2002-05-02 15:04 ]

whose2know · May 2nd, 2002, 04:05 PM

FANTASTIC! Thanks everyone for your help.

zacemmel · May 2nd, 2002, 04:05 PM

nevermind

[ This Message was edited by: zacemmel on 2002-05-02 15:06 ]

May 2nd, 2002, 03:54 PM	#5
whose2know Board Regular Join Date: May 2002 Posts: 59	I've never used cells() before only range().select I'll look up cells in the help files. Any pointers you can give me in using cells()? Is a valid statement cells(r1c1).select? __________________ Thanks, Scott

May 2nd, 2002, 03:59 PM	#6
whose2know Board Regular Join Date: May 2002 Posts: 59	how do I select B1:B44 using a cells statement? I have only been successful with selecting B1 or B44 -> cells(2,1).select __________________ Thanks, Scott

May 2nd, 2002, 04:05 PM	#8
whose2know Board Regular Join Date: May 2002 Posts: 59	FANTASTIC! Thanks everyone for your help. __________________ Thanks, Scott

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 03:40 PM	#1
whose2know Board Regular Join Date: May 2002 Posts: 59	I have built many macros where I set a loop up to select rows. Since rows are numeric, the statement > Range("A" & Start & ":AQ" & Start).Select < works where Start is the loop counter. Today I ran into the need to select columns through a loop. I was trying to find how to do it using R1C1 commands, but was unable to. I know about the offset command, but that won't work in my case. I'd appreciate any help anyone can provide.

May 2nd, 2002, 03:44 PM	#2
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	what are you actually trying to do?

May 2nd, 2002, 03:48 PM	#3
whose2know Board Regular Join Date: May 2002 Posts: 59	I'm trying to take the data in columns B to AE, copy it to another sheet (one column at at a time), sort it, delete blank rows, sort it back to original order, and then copy to a new sheet. I want to do a loop where I can select a specific column during each loop.

May 2nd, 2002, 03:50 PM	#4
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	It is much easier if you use cells() instead of range(). Cells(row number, column number).

May 2nd, 2002, 04:03 PM	#7
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Range(Cells(1,2),Cells(44,2)).select [ This Message was edited by: RET79 on 2002-05-02 15:04 ]

May 2nd, 2002, 04:05 PM	#9
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	nevermind [ This Message was edited by: zacemmel on 2002-05-02 15:06 ]