CODEWITHSUNDEEP
excelvba
Eric Cheung
Eric Cheung

Reputation: 327

Excel VBA: how to find the first row number of the Page1

I would like to find out the first row number of the page1.

Using the following code can find the last row number of page 1.

ThisWorkbook.Worksheets("Sheet1").HPageBreaks(1).Location.Row - 1

But I have to find the first row number of Page1.

Would anyone has idea on it?

Thanks a lot

Upvotes: 3

Views: 3815

Answers (4)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You could try with Sheets().PageSetup.PrintArea property. This will return the address of range for all your pages. As a result you get something like this:

$G$12:$P$31   << for continuous range/pages >> see 1st screen shot below
$G$12:$P$31;$B$5:$E$20;$R$5:$U$20   << for non-continuous ranges/pages >> see 2nd screen shot below

1st Picture: enter image description here

2nd Picture: enter image description here

Each time above the first row number is the first row number on first page. To get it you could try with one of the following solutions:

firstRow = Range(Replace(ThisWorkbook.Worksheets("Sheet1").PageSetup.PrintArea, ";", ",")).Row

or with the following solution:

firstRow = Split(Split(ThisWorkbook.Worksheets("Sheet1").PageSetup.PrintArea, ":")(0),"$")(2)

Additional explanation

Range address which you get from .PrintArea property uses semi-colon (;) to separate non-continuous ranges. We need to convert it to comma (,) if we want to use it in first solution above.

Upvotes: 1

Sam
Sam

Reputation: 7303

You can use SpecialCells to get the first visible row.

The below code will return back the address for the first visible row of the ActiveSheet

msgbox ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows(1).Address

or if you just want the row number

msgbox ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows(1).Row

Upvotes: 1

Graffl
Graffl

Reputation: 380

The following is not the nicest way of coding I have to admit. But I think, it is easy to read and it does the job (if I understood the problem correct).

Sub runningThroughSheets()

  Dim wsht As Worksheet
  Dim wholeRow As Range
  Dim counter As Integer
  Dim stopSearch As Boolean

  Set wsht = Application.ActiveWorkbook.Worksheets("Sheet1")

  counter = 0
  stopSearch = False
  Do While stopSearch = False
    If Range("A1").Offset(counter, 0).EntireRow.Hidden = False Then
        stopSearch = True
    End If
    counter = counter + 1
  Loop

  MsgBox counter

End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

This will get you the first row of the first page:

Sub dural()
    Dim nRow As Long, s As String
    Dim r As Range
    s = Sheets("Sheet1").PageSetup.PrintArea
    Set r = Range(s)
    nRow = r(1).Row
    MsgBox nRow
End Sub

Upvotes: 0

Related Questions