Count rows, split in half, assign value to cell

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,821
Messages
5,574,515
Members
412,599
Latest member
Schu94
Top