PDA

View Full Version : Solved: How do you find first Cell



jazzyt2u
12-09-2009, 08:52 PM
Hi,

I need to find the first cell at the top of each page in excel.

Please help

Thanks,
jazzyt2u

RolfJ
12-10-2009, 12:53 AM
Please clarify what you mean by 'first cell'.

In case you mean 'the non-empty cell with the smallest Row + Column value this macro would do the trick:


Sub FindFirstCellOnSheet()
Dim r As Range
Set r = ActiveSheet.UsedRange
Dim firstConstantCell As Range
Dim firstFormulaCell As Range

On Error Resume Next

Set firstConstantCell = r.SpecialCells(xlCellTypeConstants).Cells(1, 1)
Set firstFormulaCell = r.SpecialCells(xlCellTypeFormulas).Cells(1, 1)
If Not firstConstantCell Is Nothing And Not firstFormulaCell Is Nothing Then
If firstConstantCell.Row + firstConstantCell.Column < firstFormulaCell.Row + firstFormulaCell.Column Then
firstConstantCell.Select
Else
firstFormulaCell.Select
End If
ElseIf Not firstConstantCell Is Nothing Then
firstConstantCell.Select
ElseIf Not firstFormulaCell Is Nothing Then
firstFormulaCell.Select
End If

End Sub

bonyclyd
12-10-2009, 01:02 AM
Hi

I'm not sure this is what you've wanted.
The following code returns cell addresses that are in the first position of the pages.

Dim s As Worksheet
Dim pb As HPageBreak

Set s = Sheet1

For Each pb In s.HPageBreaks
Debug.Print pb.Location.Address
Next

HTH

Bob Phillips
12-10-2009, 04:52 AM
ActiveSheet.UsedRange.Cells(1, 1).Select

Simon Lloyd
12-10-2009, 05:33 AM
As you haven't asked for a VBA method simply select all tabs, click the first tab hold down Shift and click the last then Ctrl+Home would move to the first cell on every sheet.

jazzyt2u
12-14-2009, 02:06 PM
Thank you for all the suggestions and sorry I wasn't clear. bonyclyd had the code I was looking for.