auto freeze panes procedure
 

attempting to write code to set up a split and freeze panes on several
worksheets, centreing the split on the first cell with a number value ( can
be 0, but not blank, not a date (incl 2004,2005, jan-08 etc). so that the row
and column headers are static outside the split area. Area should look like
this, filled with numeric data:

jan-08 feb-08 mar-08
a b
b b
c d
d d

the problem is that typename, isnumric and isnull all fail to exclude (blank
or date or string) which is what i need to do to find the first cell with
data in it which is where the split is centered.

auto freeze panes procedure
 

Does this do what you need?

Sub split_at_Number()
Dim MyCell As Range
For Each MyCell In ActiveSheet.UsedRange
If MyCell < "" And IsNumeric(MyCell) Then
MyCell.Offset(0, 1).Select
ActiveWindow.FreezePanes = True
'With ActiveWindow 'these 4 lines will install a split at the
frozen cell
'.SplitColumn = 0
'.SplitRow = 0
'End With
End If
Next

End Sub
*-Post posted before response, posts merged!*-
There was a small typo in the code!

Sub split_at_Number()
Dim MyCell As Range
For Each MyCell In ActiveSheet.UsedRange
If MyCell < "" And IsNumeric(MyCell) Then
MyCell.Offset(0, 1).Select
ActiveWindow.FreezePanes = True
'With ActiveWindow 'these 4 lines will install a split at the frozen
cell
'.SplitColumn = 0
'.SplitRow = 0
'End With
End If
Next

End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=8033

auto freeze panes procedure
 

Thanks but i ended up using:

If Not TypeName(curcell.Value) = "Empty" _
And Not TypeName(curcell.Value) = "String" _
And Not TypeName(curcell.Value) = "Date" _
And Not curcell.Value = 2002 _
And Not curcell.Value = 2003 _
And Not curcell.Value = 2004 _
And Not curcell.Value = 2005 _
And Not curcell.Value = 2006 _
And Not curcell.Value = 2007 _
And Not curcell.Value = 2008 _
And Not curcell.Value = 2009 Then

to exclude all label cells and the blanks above the labels. However i have
now found that some of the sheet's data contains blank cells. Need to modify
logic to only exclude blank cells above the column headers. There are also
some sheets where the macro puts the split in the middle of the data, which i
can't figure out a reason for, as all the data cells are filled with numbers.

"The Code Cage Team" wrote:


Does this do what you need?

Sub split_at_Number()
Dim MyCell As Range
For Each MyCell In ActiveSheet.UsedRange
If MyCell < "" And IsNumeric(MyCell) Then
MyCell.Offset(0, 1).Select
ActiveWindow.FreezePanes = True
'With ActiveWindow 'these 4 lines will install a split at the
frozen cell
'.SplitColumn = 0
'.SplitRow = 0
'End With
End If
Next

End Sub
*-Post posted before response, posts merged!*-
There was a small typo in the code!

Sub split_at_Number()
Dim MyCell As Range
For Each MyCell In ActiveSheet.UsedRange
If MyCell < "" And IsNumeric(MyCell) Then
MyCell.Offset(0, 1).Select
ActiveWindow.FreezePanes = True
'With ActiveWindow 'these 4 lines will install a split at the frozen
cell
'.SplitColumn = 0
'.SplitRow = 0
'End With
End If
Next

End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=8033