Would like to Assign ranges for each row

tina95

New Member
Joined
Apr 4, 2009
Messages
8
Hi All,

I am defining ranges for cells in a row by the formula:
Set myrange3 = Range("B13", Cells(13, Columns.Count).End(xlToLeft))
Set myrange4 = Range("B14", Cells(14, Columns.Count).End(xlToLeft))
Set myrange5 = Range("B15", Cells(15, Columns.Count).End(xlToLeft))
Set myrange6 = Range("B16", Cells(16, Columns.Count).End(xlToLeft))
Set myrange7 = Range("B17", Cells(17, Columns.Count).End(xlToLeft))
.
.
.
.
.
Set myrange62 = Range("B72", Cells(72, Columns.Count).End(xlToLeft))

The last one is 62 assuming data wont go over 62nd line. However, there would be always a room for error if this is done manually. Is there any way, I could assign range variables only till that row which has data
For example
The data below shows I only need to define range variables namely myrange1 including cells 2 and 5
till
myrange7 of cell values 9 and 6.

A B
2 5
3.4 8
5 12
6 23
77 56
8 7
9 6


The syntax:
LastRow = Selection.SpecialCells(xlCellTypeLastCell).Row
helps by giving the number of the last valid data row, but I am not sure how this might be used if at all in automatically assigning ranges of valid row cells to variables.
Would some one please guide me in this?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Natalie (now I know it's not Tina!)

Does this relate to your previous question?

It would rarely, if ever, be necessary to set so many variables like that. There would normally be some way to save having to do all that.

It is good that you are trying to get started yourself, but best if you can also describe just what you are trying to do so that better suggestions can possibly be made.

Do/can all your rows have a different number of entries?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings Tina,

I think I followed what you are saying. It might be beneficial to show us the procedure in its entirety, so we can maybe see better how to rectify.

Mark
 

tina95

New Member
Joined
Apr 4, 2009
Messages
8
Thank you so much for your replies.

The problem situation is to highlight the maximum value in each row . Each row would have the same no. of columns of data.

A B C D

ir -0.7 0.30 0.25
ia 0.7 0.23 0.24
hh -0.69 0.24 0.17
h 0.23 0.77 0.22
a 0.24 0.71 0.23
q 0.31 0.72 0.3
u 0.3 0.22 0.63
b 0.4 0.3 0.59

My approach is I assigned all the valid data cells of each row to a range variable names myrange1, myrange2, myrange3 ....myrange62

Set myrange1 = Range("B11", Cells(11, Columns.Count).End(xlToLeft))
Set myrange2 = Range("B12", Cells(12, Columns.Count).End(xlToLeft))


myMax1 = Abs(WorksheetFunction.Max(myrange1))
myMax2 = Abs(WorksheetFunction.Max(myrange2))

.
.
.
myMax62 = Abs(WorksheetFunction.Max(myrange3))


myMin1 = Abs(WorksheetFunction.Min(myrange1))
myMin2 = Abs(WorksheetFunction.Min(myrange2)).
.
.
Maxx1 = IIf(myMax1 > myMin1, myMax1, myMin1)
Maxx2 = IIf(myMax2 > myMin2, myMax2, myMin2)
...


The last row I am assuming is 62 as data would not go over 62nd line in my case . However, no. of rows would always change in every unique case and there would be always a room for error if data indeed goes beyond 62nd row. Is there any way, I could assign range variables only till that row which has data
For example
The data below implies I only need to define range variables namely
Myrange1 = range("A1:B1")
till
Myrange7 = range("A7:B7")

A B
2 5
3.4 8
5 12
6 23
77 56
8 7
9 6


The syntax:
LastRow = Selection.SpecialCells(xlCellTypeLastCell).Row
helps by giving the number of the last valid data row, but I am not sure how this might be used if at all in automatically assigning ranges of valid row cells to variables.


Once I assign ranges , the next step I did is to format the absolute value of cell IN EACH ROW. As of now, I am defining as many For loops as there are valid rows.

For Each c In myrange1
If Abs(c.Value) = Maxx1 Then
c.font.Bold = True
c.Interior.ThemeColor = xlThemeColorDark1
c.Interior.TintAndShade = -0.499984740745262
c.Select
Set obj1 = Selection ' Getting the object reference so I could check
' if the next below rows' maximum value's object ' belongs to the same column, if not, then will ' apply border Eg. -0.69 and its ID is bordered.
End If
Next c
' MsgBox Maxx1
End Sub


For Each c In myrange2
If Abs(c.Value) = Maxx2 Then
c.font.Bold = True
c.Interior.ThemeColor = xlThemeColorDark1
c.Interior.TintAndShade = -0.499984740745262
c.Select
Set obj2 = Selection
End If
Next c
End Sub
.
.
.
(till myrange62)

However, after running the entire code, I also get the error Too large for a procedure because of the repetition.

If some one could please let me know an efficient way of doing this, I would really appreciate it.
 

Jacophile

Board Regular
Joined
Mar 8, 2009
Messages
51

ADVERTISEMENT

Hi, why not just use conditional formatting? You just need to be careful with the dollar signs...
 

tina95

New Member
Joined
Apr 4, 2009
Messages
8
Hi Jacophile,

Conditional formatting wont know where to give an underline since underlining needs to be done for the corresponding ID also.
Like this, there would be 25 or more worksheets to be done in any unique project, so I thought automating with VBA would be a huge time saver in long run even if I have just stated learning it.
If I could get some leads on the problem, it would be highly appreciated.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Natalie

Is it possible that more than one value in a row could have equal highest absolute value?
For example, could a row be like below? If so, what should happen about bolding/underlining in a situation like that?

Excel Workbook
ABCDE
10
11hh-0.69-0.20.69
12
CF
 

tina95

New Member
Joined
Apr 4, 2009
Messages
8
Hi Peter,
I highly appreciate your reply.
Since the analysis results always comes sorted by size, it would rarely happen that two numbers of same row would have the same value. But in rare situations, it would be to bold/underline border both of them in that case.

Regards,
Natalie
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,
I highly appreciate your reply.
Since the analysis results always comes sorted by size, it would rarely happen that two numbers of same row would have the same value. But in rare situations, it would be to bold/underline border both of them in that case.

Regards,
Natalie
Not sure this is perfect, but let's see if it is on the right track.

Instead of effectively calculating the number of columns (using the .End(xlToLeft) idea) every row, just do it once since all your rows have the same number of columns.

Instead of having a zillion variables to define the range (myRange) each row, just have one that is used over and over in a loop.

Anyway, give this a test on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Max_Abs()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> myMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>, myMin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>, Maxx1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> MaxCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastMaxCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, sr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FoundMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    sr = 11<br>    lc = Cells(sr, Columns.Count).End(xlToLeft).Column<br>    lr = Range("B" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("A11").Resize(lr - sr + 1, lc)<br>        .Font.Bold = <SPAN style="color:#00007F">False</SPAN><br>        .Borders.LineStyle = xlNone<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> r = sr <SPAN style="color:#00007F">To</SPAN> lr + 1<br>        <SPAN style="color:#00007F">Set</SPAN> myRange = Range("B" & r).Resize(1, lc - 1)<br>        myMax = Abs(WorksheetFunction.Max(myRange))<br>        myMin = Abs(WorksheetFunction.Min(myRange))<br>        Maxx1 = IIf(myMax > myMin, myMax, myMin)<br>        c = lc<br>        FoundMax = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Do</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Abs(Cells(r, c).Value) = Maxx1 <SPAN style="color:#00007F">Then</SPAN><br>                FoundMax = <SPAN style="color:#00007F">True</SPAN><br>                Cells(r, c).Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>                MaxCol = c<br>                <SPAN style="color:#00007F">If</SPAN> r > sr <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> MaxCol <> LastMaxCol <SPAN style="color:#00007F">Or</SPAN> r = lr + 1 <SPAN style="color:#00007F">Then</SPAN><br>                        <SPAN style="color:#00007F">With</SPAN> Cells(r - 1, 1).Resize(1, LastMaxCol).Borders(xlEdgeBottom)<br>                            .LineStyle = xlContinuous<br>                            .Weight = xlThin<br>                            .ColorIndex = xlAutomatic<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                        LastMaxCol = MaxCol<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Else</SPAN><br>                    LastMaxCol = MaxCol<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            c = c - 1<br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> FoundMax = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

tina95

New Member
Joined
Apr 4, 2009
Messages
8
Re: Would like to Assign ranges for each row- You are Genius

Dear Peter,

I really thank you very much for helping me solve this. This works great on my project and it is very efficient.
You are Genius!!!!!

Regards,

Natalie
 

Watch MrExcel Video

Forum statistics

Threads
1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top