Solved: How do you find first Cell

[jazzyt2u]

Hi,

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

Please help

Thanks,
jazzyt2u

[RolfJ]

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]

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]

ActiveSheet.UsedRange.Cells(1, 1).Select

[Simon Lloyd]

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]

Thank you for all the suggestions and sorry I wasn't clear. bonyclyd had the code I was looking for.