# Would like to Assign ranges for each row

#### tina95

##### New Member
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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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
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.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.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

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

#### tina95

##### New Member
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

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
Hi Peter,
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
Hi Peter,
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,533
Messages
5,854,291
Members
431,636
Latest member
shabbas313

### 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.

### Which adblocker are you using?

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

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