MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pagebreak/Cell Problem

Posted by Kevin Tweddle on December 25, 2001 12:34 AM


I have written a Makro that will read a text file into sheet 1 of an excel file. The list contains users and their departments along with the amount of files they have on the server, this is known as File in MB.

I have two problems:

1) The list is updated every day and so users are added to the list, this means that the File in MB column changes constantly ie values are added. I have written makros that filter out each department and calculate the total usage of files for that department, there are in total 15 departments. Because the cells are updating I have written the makro so that for the column File in MB (Column Q) I calculate everey cell ie from the cell where the data starts to cell Q65535. My question is this, is there an easier way to write the program so that it checks the cells to see what has values and what is empty and then calculate only the cells with values.

2) My second problem is that I have archives that shows each department and their file usage, from these archives I have four 3D Pie graphs showing their percentages, the graphs need to be large so that they can be readable but the problem is I do not know how to write a makro that adhjusts the pagebreak so that everything fits onto the one page. I have tried it manually and the graphs fit, but I would like to know if there is a code that will remove the pagebreaks ie so that everything will fit onto page one. I have noticed that the Vertical Pagebreak is at Column E and the Horizontal is at Row 49. Can anybody help?

This is for Excel 97 (Office 97)

I would most appreciate any assisatance.


Posted by Ivan F Moala on December 25, 2001 2:48 AM

HI Kevin
How about this........

Sub FilesInMB()
Dim Rg As Range
Dim cellRg As Range
Dim x

Set Rg = Columns("Q:Q").SpecialCells(2, 1)
For Each cellRg In Rg
'Do your thing here
End Sub

Sub q2()

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub

any help.....otherwise repost