Count rows, split in half, assign value to cell

[cgmojoco]

Hi there-

Would like a macro that would do the following process to each sheet in the workbook.

Count the number of rows that have data in them (excluding row one), then divide the rows by 2 (not necessarily always an even number of rows-Round up?):

1st half would have value, user 1 added to cells in column H for each row
2nd hafl would have value, user 2 added to cells in column H for each row

Next sheet would be selected and same operation would run.

This code would allow me to skip specific sheets. I would like to skip sheets labeled MITCHELLMOD and MITCHELLRX

Then I would like to freeze panes on each sheet with A2 as the active sheet when freezing. (not skipping sheets)

Thanks in advance.

 

[acw]

Hi

How about

Sub aaa()
 For Each sh In ActiveWorkbook.Sheets
  If sh.Name <> "MITCHELLMOD" And sh.Name <> "MITCHELLRX" Then
   lastcell = sh.Cells.SpecialCells(xlCellTypeLastCell).Row
   sh.Range("H2:H" & Int(lastcell / 2)).Value = "user 1"
   sh.Range("h" & Int(lastcell / 2) + 1 & ":h" & lastcell).Value = "user 2"
  End If
 sh.Select
 Range("a2").Select
 ActiveWindow.FreezePanes = True
Next sh

End Sub

Tony

 

[cgmojoco]

What an elegant solution, that did the trick