Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 freeze panes won't freeze top row only | Excel Discussion (Misc queries) | |||
Freeze Panes | Excel Discussion (Misc queries) | |||
Freeze 2 Panes | Excel Discussion (Misc queries) | |||
Freeze Panes | Setting up and Configuration of Excel | |||
Freeze Panes | Excel Discussion (Misc queries) |