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?
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?