View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Gareth[_3_] Gareth[_3_] is offline
external usenet poster
 
Posts: 109
Default 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