How to identify page breaks in excel with VBA, varying conditions -
this problem i've been wrestling time. forgive me not citing exact threads i've researched, i've not been keeping log. code sample have below has been constructed reviewing many threads on topic. however, exact solution need still evades me.
simply put: in excel vba, need able identify last row , first/last column of each page in long table 1 5 pages long (horizontally), after having been dynamically created , populated. appreciate quick way identify populated data stops, can generating routine if there's no easy solution. following code identify correct final row , column of single page table... once. can make modification needed check subsequent pages multi-page table. real problem i'm having once cell has been populated , cleared, excel includes cell in range of used cells. subsequent executions of same code fail, because last cell no longer correctly identified. there anyway reverse this, or different approach take?
i not populating cells , deleting them find page breaks, i've not found solution avoids doing so. guidance might provide. ,mike shanahan
sub findpagebreaks() dim x hpagebreaks, pb_x hpagebreak dim y vpagebreaks, pb_y vpagebreak, pagematrix() integer dim lastpopulated(1) integer, test integer, target integer redim pagematrix(1 1, 0 2) integer activesheet debug.print "============================================" debug.print "horizontal page breaks" set x = .hpagebreaks debug.print "initial hbreaks: ", x.count lastpopulated(0) = .cells.specialcells(xlcelltypelastcell).row debug.print "last row of data: ", lastpopulated(0) target = x.count + 1 test = lastpopulated(0) while x.count < target test = test + 10 .cells(test, 1).value = "." debug.print "cell: " & .cells(test, 1).address & " populated" if test > 100 exit loop each pb_x in x if pb_x.extent = xlpagebreakfull debug.print "row: " & pb_x.location.row, "full page break" pagematrix(1, 2) = pb_x.location.row - 1 else debug.print "row: " & pb_x.location.row, "partial page break" end if next pb_x .range(.cells(lastpopulated(0) + 1, 1), .cells(test, 1)).clearcontents debug.print "cells: " & .range(.cells(lastpopulated(0), 1), .cells(test, 1)).address & " cleared." debug.print "horizontal exploration complete." debug.print "vertical page breaks" set y = .vpagebreaks debug.print "initial vbreaks: ", y.count lastpopulated(1) = .cells.specialcells(xlcelltypelastcell).column debug.print "last column of data: ", lastpopulated(1) target = y.count + 1 test = lastpopulated(1) while y.count < target test = test + 10 .cells(1, test).value = "." debug.print "cell: " & .cells(1, test).address & " populated" if test > 100 exit loop pagematrix(1, 0) = 1 each pb_y in y if pb_y.extent = xlpagebreakfull debug.print "column: " & pb_y.location.column, "full page break" pagematrix(1, 1) = pb_y.location.column - 1 else debug.print "row: " & pb_y.location.column, "partial page break" end if next pb_y .range(.cells(1, lastpopulated(1) + 1), .cells(1, test)).clearcontents debug.print "cells: " & .range(.cells(1, lastpopulated(1)), .cells(1, test)).address & " cleared." debug.print "vertical exploration complete." debug.print "page", "first col", "last col", "last row" debug.print 1, pagematrix(1, 0), pagematrix(1, 1), pagematrix(1, 2) debug.print "~~~~~~~~~~~~~~~~~~~~~~" debug.print "sub complete." debug.print end end sub
not complete answer, reduces code.
sure seems lot of code find page breaks. page breaks, try simple routine. modify situation.
sub sample() 'horizontal pagebreaks h = 1 100 if sheets("sheet1").rows(h).pagebreak <> xlpagebreaknone msgbox "hor " & n next 'vertical pagebreaks v = 1 100 if sheets("sheet1").columns(v).pagebreak <> xlpagebreaknone msgbox "ver " & n next end sub 
Comments
Post a Comment