Would like to Assign ranges for each row

[tina95]

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?

 

[Peter_SSs]

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]

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]

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]

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

 

[tina95]

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]

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
	A	B	C	D	E
10
11	hh	-0.69	-0.2	0.69
12
CF

 

[tina95]

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]

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]

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