Hi,
Instead of using Application.Union,
can we select multiple column using Range("xx,xx,xx,xx,xx") with upper bound (j) & lower bound(i) ?
Application.Union(Range("EP" & j & ":EP" & i), _ Range("ER" & j & ":ER" & i), _ Range("ET" & j & ":ET" & i), _ Range("EV" & j & ":EV" & i), _ Range("EX" & j & ":EY" & i)).Select
I have tried this but got error
can anyone help me?Range("EP & j & :EP & i, ER & j & :ER & i, ET & j & :ET & i, EV & j & :EV & i, EX & j & :EY" & i).Select
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
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
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hi Ciao,
I am not aware that it was duplicated.
sorry for inconveniences caused.
What's wrong with the Union approach?
Another alternative
intersect(rows(i).resize(j-i+1),range("EP:EP,ER:ER,ET:ET,EV:EV,EY:EY")).select
Hi Andy,
Nothing wrong with the Union approach, just look for alternative method.
It has been resolved
but your alternative methodRange("EP" & j & ":EP" & i & ",ER" & j & ":ER" & i & ",ET" & j & ":ET" & i & ",EV" & j & ":EV" & i & ",EX" & j & ":EY" & i).Select
does not workintersect(rows(i).resize(j-i+1),range("EP:EP,ER:ER,ET:ET,EV:EV,EY:EY")).select
works fine if you have valid i and j values.
Of course i and j is valid because both approach below working fine
Range("EP" & j & ":EP" & i & ",ER" & j & ":ER" & i & ",ET" & j & ":ET" & i & ",EV" & j & ":EV" & i & ",EX" & j & ":EY" & i).Select Application.Union(Range("EP" & j & ":EP" & i), _ Range("ER" & j & ":ER" & i), _ Range("ET" & j & ":ET" & i), _ Range("EV" & j & ":EV" & i), _ Range("EX" & j & ":EY" & i)).Select
I missed the 2 column spread at EX:EY, is that what you meant?Intersect(Rows(i).Resize(j - i + 1), Range("EP:EP,ER:ER,ET:ET,EV:EV,EX:EY")).select
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,
or maybe you have missed any syntax?Upper bound, j = Range("G9:G" & Rows.Count).End(xlDown).Row Lower bound, i = Range("G" & Rows.Count).End(xlUp).Row
These 3 lines all give me the same result.
The attached actually puts values in the range.Sub Test() Dim J, I J = Range("G9:G" & Rows.Count).End(xlDown).Row I = Range("G" & Rows.Count).End(xlUp).Row Range("EP" & J & ":EP" & I & ",ER" & J & ":ER" & I & ",ET" & J & ":ET" & I & ",EV" & J & ":EV" & I & ",EX" & J & ":EY" & I).Select Application.Union(Range("EP" & J & ":EP" & I), _ Range("ER" & J & ":ER" & I), _ Range("ET" & J & ":ET" & I), _ Range("EV" & J & ":EV" & I), _ Range("EX" & J & ":EY" & I)).Select Intersect(Rows(I).Resize(J - I + 1), Range("EP:EP,ER:ER,ET:ET,EV:EV,EX:EY")).Select End Sub
Hi Andy,
Please refer to my attached file.
Using both approach it works fine but not for intersect.
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.
Intersect(Rows(j).Resize(i - j + 1), Range("EP:EP,ER:ER,ET:ET,EV:EV,EX:EY")).Copy
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?
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,
It selects the row nearest the top, which you consider upper but again in code terms I see this as lower.j = Range("G9").End(xlDown).Row
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
instead ofj = Range("G9:G" & Rows.Count).End(xlDown).Row,
because it is for filtering function, am I right or wrong?j = Range("G9").End(xlDown).Row
What filtering? There is no filtering in the workbook you posted so it's difficult to have what difference it would make.
Hi Andy,
Please check carefully the workbook I have attached,
Filtered at rows 12, but due rows 9 to 12 have been merged,FilterFunction: If ws.Name = "ANNEXURE 1 (ii)" Then ActiveSheet.AutoFilterMode = False With ActiveSheet .Range("A12:TB" & i).AutoFilter Field:=2, Criteria1:="Business Banking" .Range("A12:TB" & i).AutoFilter Field:=5, Criteria1:="HUNTER" End With End If
my declaration as follow;
i = Range("G" & Rows.Count).End(xlUp).Row
j = Range("G9:G" & Rows.Count).End(xlDown).Row
So actually j is not variable. It will always be 13.
I thought j will not only remain at rows 13, if the data at rows 13 does not contains filtered criteria,
So j will go down from "G9" until it founds non-empty cells?With ActiveSheet .Range("A12:TB" & i).AutoFilter Field:=2, Criteria1:="Business Banking" .Range("A12:TB" & i).AutoFilter Field:=5, Criteria1:="HUNTER" End With
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks