VBA code to select multiple columns with upper bound (j) & lower bound(i)

**Faridwahidi** · 07-12-2014, 12:12 AM

Hi,

Instead of using Application.Union,

Please Login or Register  to view this content.

can we select multiple column using Range("xx,xx,xx,xx,xx") with upper bound (j) & lower bound(i) ?
I have tried this but got error

Please Login or Register  to view this content.

can anyone help me?

**Ed_Collins** · 07-12-2014, 12:49 AM

Aren't you forgetting a bunch of quote marks? Try this:

Range("EP & j & ":EP" & i, "ER" & j & ":ER" & i, "ET" & j & ":ET" & i, "EV" & j & ":EV" & i, "EX" & j & ":EY" & i).Select

**HaHoBe** · 07-12-2014, 01:22 AM

Hi, Faridwahidi,

please note if you create two threads of the same topic by mistake (http://www.excelforum.com/excel-prog...-bound-i.html] and don´t let people work in vain.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules.

Ciao,
Holger

**Faridwahidi** · 07-12-2014, 03:37 AM

Hi Ciao,

I am not aware that it was duplicated.

sorry for inconveniences caused.

**Andy Pope** · 07-12-2014, 04:59 AM

What's wrong with the Union approach?

Another alternative

Please Login or Register  to view this content.

**Faridwahidi** · 07-12-2014, 05:51 AM

Hi Andy,

Nothing wrong with the Union approach, just look for alternative method.

It has been resolved

Please Login or Register  to view this content.

but your alternative method

Please Login or Register  to view this content.

does not work

**Andy Pope** · 07-12-2014, 05:54 AM

works fine if you have valid i and j values.

**Faridwahidi** · 07-12-2014, 06:04 AM

Of course i and j is valid because both approach below working fine

Please Login or Register  to view this content.

**Andy Pope** · 07-12-2014, 08:36 AM

Please Login or Register  to view this content.

I missed the 2 column spread at EX:EY, is that what you meant?

**Faridwahidi** · 07-12-2014, 12:29 PM

Hi Andy,

I don't know what went wrong with intersect approach. I have already changed the missing columns before but still not working

j and i definitely valid,

Please Login or Register  to view this content.

or maybe you have missed any syntax?

**Andy Pope** · 07-14-2014, 04:00 AM

These 3 lines all give me the same result.

Please Login or Register  to view this content.

The attached actually puts values in the range.

**Faridwahidi** · 07-14-2014, 04:35 AM

Hi Andy,

Please refer to my attached file.

Using both approach it works fine but not for intersect.

**Andy Pope** · 07-14-2014, 04:43 AM

Did you check the actual values of i and j? If so you would have seen that they were reversed compared to what you stated originally, "upper bound (j) & lower bound(i)"
In fact j is the lower and i upper.

Please Login or Register  to view this content.

**Faridwahidi** · 07-14-2014, 05:17 AM

Hi Andy,

It works now

but I am a bit confused,

i = Range("G" & Rows.Count).End(xlUp).Row,
it will go up and stop if found non-empty cells in column "G" (the last row in column "G"), shoud be lower bound ?

And

j = Range("G9:G" & Rows.Count).End(xlDown).Row,
From "G9" it will go to down and stop if found non-empty cells in column "G", shoud be upper bound?

**Andy Pope** · 07-14-2014, 05:33 AM

Well I guess it depends on what we mean by upper and lower.

For me, in programming context, lower is start point and upper is end point.

In your code, i comes up from last row and returns 27 or end of data. This is lower down the sheet but I consider this the upper value for coding.

Your j code could be,

Please Login or Register  to view this content.

It selects the row nearest the top, which you consider upper but again in code terms I see this as lower.

**Faridwahidi** · 07-15-2014, 09:41 AM

Hi Andy,

Thanks for the opinion, you are at expertise level but I am only at beginner level. Too many things I need to learn.
I take note your advise when dealing with other expertise in future.

Just one more question,

I am using

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

because it is for filtering function, am I right or wrong?

**Andy Pope** · 07-15-2014, 09:50 AM

What filtering? There is no filtering in the workbook you posted so it's difficult to have what difference it would make.

**Faridwahidi** · 07-15-2014, 10:23 AM

Hi Andy,

Please check carefully the workbook I have attached,

Please Login or Register  to view this content.

Filtered at rows 12, but due rows 9 to 12 have been merged,
my declaration as follow;

i = Range("G" & Rows.Count).End(xlUp).Row
j = Range("G9:G" & Rows.Count).End(xlDown).Row

**Andy Pope** · 07-15-2014, 10:26 AM

So actually j is not variable. It will always be 13.

**Faridwahidi** · 07-15-2014, 10:47 AM

I thought j will not only remain at rows 13, if the data at rows 13 does not contains filtered criteria,

Please Login or Register  to view this content.

So j will go down from "G9" until it founds non-empty cells?
j = Range("G9:G" & Rows.Count).End(xlDown).Row

or I can use, j = Range("G9").End(xlDown).Row

correct me if I am wrong.

**Andy Pope** · 07-15-2014, 10:56 AM

Without going through your code in detail I don't see you doing any special to excluded filtered cells.

So just stick with the method you currently have in order to determine top row, if it works.

VBA code to select multiple columns with upper bound (j) & lower bound(i)

LinkBack

Thread Tools

Rate This Thread

Display

VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Re: VBA code to select multiple columns with upper bound (j) & lower bound(i)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can you declare only a lower bound for an array and let the upper bound float?

Percentile Array with an upper and lower bound

Forms, Bound Columns etc

[SOLVED] Data Bound Controls in VBA??

[SOLVED] How to get the coordinate of the lower-bound cell in Excel 2003 with C#

Bookmarks

Bookmarks

Posting Permissions