Hi there, the code below works out the amount of rows on each sheet
and
divides it up percentage(wise) working from the top of the sheet,
starting
at 0% right down to the bottom of the used rows, ending at 100% on
each
range for each sheet...my question is:
Can this be turned upside down without using a 'sort' macro as it is
already sorted before the %ages are added..
So the top of the sheet/range is 100% and the bottom is 0%...

Thanks for any help anyone can give...cheers, ste

Sub getdepth()

Application.ScreenUpdating = False

For sheetNumber = 1 To 56

SheetName = "S" & Format(sheetNumber, "##0")
Sheets(SheetName).Select

xlrow = 3

Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")

ActiveSheet.Cells(xlrow, 10).Value = Format((xlrow /
ActiveSheet.Cells(1, 2).Value), "#0.0%")

xlrow = xlrow + 1

Application.StatusBar = "System Status: " & Format(xlrow
/ ActiveSheet.Cells(1, 2), "00%") & " of Depth %age for " & SheetName
& " have been built..."

Loop

Next

Application.StatusBar = False

Sheets("Selection").Select

checksystem_forblanks

End Sub

Mac,

Calculate the last row of data

Cells(Rows.Count,"A").End(xlUp).Row

and then run the lop backwards from that value to row 3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ste mac" wrote in message
om...
Hi there, the code below works out the amount of rows on each sheet
and
divides it up percentage(wise) working from the top of the sheet,
starting
at 0% right down to the bottom of the used rows, ending at 100% on
each
range for each sheet...my question is:
Can this be turned upside down without using a 'sort' macro as it is
already sorted before the %ages are added..
So the top of the sheet/range is 100% and the bottom is 0%...

Thanks for any help anyone can give...cheers, ste

Sub getdepth()

Application.ScreenUpdating = False

For sheetNumber = 1 To 56

SheetName = "S" & Format(sheetNumber, "##0")
Sheets(SheetName).Select

xlrow = 3

Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")

ActiveSheet.Cells(xlrow, 10).Value = Format((xlrow /
ActiveSheet.Cells(1, 2).Value), "#0.0%")

xlrow = xlrow + 1

Application.StatusBar = "System Status: " & Format(xlrow
/ ActiveSheet.Cells(1, 2), "00%") & " of Depth %age for " & SheetName
& " have been built..."

Loop

Next

Application.StatusBar = False

Sheets("Selection").Select

checksystem_forblanks

End Sub

Wouldn't that still apply the same logic per row, but in reverse?

I thought it would look something like:
(lastrow - xlrow) / ActiveSheet.Cells(1, 2).Value


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Mac,

Calculate the last row of data

Cells(Rows.Count,"A").End(xlUp).Row

and then run the lop backwards from that value to row 3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ste mac" wrote in message
om...
Hi there, the code below works out the amount of rows on each sheet
and
divides it up percentage(wise) working from the top of the sheet,
starting
at 0% right down to the bottom of the used rows, ending at 100% on
each
range for each sheet...my question is:
Can this be turned upside down without using a 'sort' macro as it is
already sorted before the %ages are added..
So the top of the sheet/range is 100% and the bottom is 0%...

Thanks for any help anyone can give...cheers, ste

Sub getdepth()

Application.ScreenUpdating = False

For sheetNumber = 1 To 56

SheetName = "S" & Format(sheetNumber, "##0")
Sheets(SheetName).Select

xlrow = 3

Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")

ActiveSheet.Cells(xlrow, 10).Value = Format((xlrow /
ActiveSheet.Cells(1, 2).Value), "#0.0%")

xlrow = xlrow + 1

Application.StatusBar = "System Status: " & Format(xlrow
/ ActiveSheet.Cells(1, 2), "00%") & " of Depth %age for " & SheetName
& " have been built..."

Loop

Next

Application.StatusBar = False

Sheets("Selection").Select

checksystem_forblanks

End Sub

Hi Bob!

And I would have bet my beer money that you'd say, "Send it to
Norman!"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I'm not thinking that straight this late.

Bob

"Norman Harker" wrote in message
...
Hi Bob!

And I would have bet my beer money that you'd say, "Send it to
Norman!"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Thanks Bob and Rob for your input, l didn't think this one would
be as easy as it sounds, thanks for your help anyway..

seeya ste


"Bob Phillips" wrote in message ...
I'm not thinking that straight this late.

Bob

"Norman Harker" wrote in message
...
Hi Bob!

And I would have bet my beer money that you'd say, "Send it to
Norman!"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.