Help with some code to insert rows
 

I want to find the maximum value in columns N, Y, AJ, AW, BH, BS and CD

It can from 1 to 10, depending on th value I want to insert a row below.

I had thought of starting it sometrhing like this:

For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)
'find the max minus 1
cell.offset(1,0).resize(max minus 1,1).EntireRow.Insert
Next cell

I cannot work out the code to calculate the max -1 figure.

Any help gratefully received.

Gareth

Help with some code to insert rows
 

mymax = Application.Max(Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH,B S:BS,CD:CD"))

if you mean for that specific row (assume i is the row you want to check)

set rng = Intersect(rows(i),Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH ,BS:BS,CD:CD"))
myMax = Application.Max(rng)



--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
I want to find the maximum value in columns N, Y, AJ, AW, BH, BS and CD

It can from 1 to 10, depending on th value I want to insert a row below.

I had thought of starting it sometrhing like this:

For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)
'find the max minus 1
cell.offset(1,0).resize(max minus 1,1).EntireRow.Insert
Next cell

I cannot work out the code to calculate the max -1 figure.

Any help gratefully received.

Gareth

Help with some code to insert rows
 

After posting I decided to have a go myself and came up with the following,
which appears to work:

Sub SortCopyFormat()
Application.ScreenUpdating = False
With Worksheets("Hidden")
For Each cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
numrow = Application.Max(cell.Offset(0, 13), cell.Offset(0, 24),
cell.Offset(0, 35), cell.Offset(0, 48), cell.Offset(0, 59), cell.Offset(0,
70), cell.Offset(0, 82)) - 1
If numrow = 1 Then
cell.Offset(1, 0).Resize(numrow, 1).EntireRow.Insert
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Is there any way to simplify/improve it?

Gareth

"Tom Ogilvy" wrote in message
...
mymax = Application.Max(Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH,B S:BS,CD:CD"))

if you mean for that specific row (assume i is the row you want to check)

set rng =
Intersect(rows(i),Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH ,BS:BS,CD:CD"))
myMax = Application.Max(rng)



--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
I want to find the maximum value in columns N, Y, AJ, AW, BH, BS and CD

It can from 1 to 10, depending on th value I want to insert a row below.

I had thought of starting it sometrhing like this:

For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)
'find the max minus 1
cell.offset(1,0).resize(max minus 1,1).EntireRow.Insert
Next cell

I cannot work out the code to calculate the max -1 figure.

Any help gratefully received.

Gareth