While developing a report in Excel .I faced a scenario which needs to count the page breaks in excel sheet for that we have a method hpagebreaks.count
pagebreak = Worksheets("Sheet1").HPageBreaks.count
By the above line we can get the count of the page breaks in sheet 1.
but the intention is to put the text in the above cell of each page break till the used range of the sheet.
so basically it took me arround 3 hours to crack the solutions for this I am looping each cell in the column it is processing properly, till the first page break then it is throwing an error some times it is going to third page ,throwing error.
Then i understood the meaning of excel "perform exceptionally well" !
Logic ,it is processing up to the active cell means. Let us say if your pointer is placed in first column 9th row it will loop till the 9th row if there is any page break after it will shows the error message.
so we need to push the active cell beyond the used range
sheet2.Activate
ActiveWindow.ScrollRow = LastRow
the above two lines will activate the sheet 2 and pushes the active cell to last row which is nothing but the above used range we pass like 86.
For count = 1 To page break
brkr = sheet1.HPageBreaks(count).Location.Address ' this will give the address of the page break
myvalue = sheet1.Range(replac).Value ' this will give the value in the cell
if myvalue ="" then
your logic hear............
else
your logic hear.......
end if
Next count
this way you can achieve the task on page breaks in Excel!
finally what i understood is
Working on Excel needs more Excitement!
Further Reading on Excel 2013
