How do I find the first unfrozen row in Excel using VBA?
 

This is probably so easy but I'm more experianced in VBA for Word. I have a
large workbook (33 sheets) that is used as a template. Each sheet is frozen
at a different line, due to informaton above the freeze point that is
different for each sheet. I need to determine what the first unfrozen row is
for formatting purposes, but for the life of me can't figure it out. Is
there an equivilent to "SplitRow" like "FreezeRow" or do I have to try and do
something wierd like "SendKeys(^{HOME})"?

Thanks,

How do I find the first unfrozen row in Excel using VBA?
 

Sub di()
MsgBox ActiveWindow.SplitRow
MsgBox ActiveWindow.SplitColumn
End Sub

"SteveC" wrote:

This is probably so easy but I'm more experianced in VBA for Word. I have a
large workbook (33 sheets) that is used as a template. Each sheet is frozen
at a different line, due to informaton above the freeze point that is
different for each sheet. I need to determine what the first unfrozen row is
for formatting purposes, but for the life of me can't figure it out. Is
there an equivilent to "SplitRow" like "FreezeRow" or do I have to try and do
something wierd like "SendKeys(^{HOME})"?

Thanks,

How do I find the first unfrozen row in Excel using VBA?
 

Should have noted that the first unfrozen row will be the next one down.

Sub di()
MsgBox ActiveWindow.SplitRow
firstUnfrozenRow = ActiveWindow.SplitRow + 1
End Sub

"SteveC" wrote:

This is probably so easy but I'm more experianced in VBA for Word. I have a
large workbook (33 sheets) that is used as a template. Each sheet is frozen
at a different line, due to informaton above the freeze point that is
different for each sheet. I need to determine what the first unfrozen row is
for formatting purposes, but for the life of me can't figure it out. Is
there an equivilent to "SplitRow" like "FreezeRow" or do I have to try and do
something wierd like "SendKeys(^{HOME})"?

Thanks,

How do I find the first unfrozen row in Excel using VBA?
 

Thanks for responding, but I tried that. It always returns "1" since
SplitRow appears to be dependent on window visibility i.e. I do
<Window/Freeze Panes at row 75 with row 63 as the first visible row at the
top. ActiveWindow.SplitRow returns 12.

Currently I'm ensuring that only the last row of the frozen pane is visible
and then doing:

beginRow = ActiveWindow.Panes(1).ScrollRow + 1

Provided the template dosen't get changed this works, but I was hoping for a
more robust solution that didn't depend on visibility.

Thanks again.

"JLGWhiz" wrote:

Should have noted that the first unfrozen row will be the next one down.

Sub di()
MsgBox ActiveWindow.SplitRow
firstUnfrozenRow = ActiveWindow.SplitRow + 1
End Sub

"SteveC" wrote:

This is probably so easy but I'm more experianced in VBA for Word. I have a
large workbook (33 sheets) that is used as a template. Each sheet is frozen
at a different line, due to informaton above the freeze point that is
different for each sheet. I need to determine what the first unfrozen row is
for formatting purposes, but for the life of me can't figure it out. Is
there an equivilent to "SplitRow" like "FreezeRow" or do I have to try and do
something wierd like "SendKeys(^{HOME})"?

Thanks,