Replies

If there is data beside the column you select, XL97 warns you there is data next to your selection and it will not be sorted.

Agreed Stephen, I had never used the formula so I had not looked at it critically.

Since XL spits the dummy if you try to make a PT with a column with no heading, counting the number of non-blank cells in Row 1 will always be a valid way of determining the number of columns. The easiest way of doing this is COUNTA($1:$1). So if you make sure there are no blanks in Column A the following should define the datarange alright:

Datarange = OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)

Defining data ranges if there may be blank rows present is a bit of a problem, the following solution is not terribly elegant but I have not been able to make it fail yet.

Start your data range in cell B1. In cell A1 put the following formula:

=IF(COUNTA(B1:IV1)>0,ROW(),0)

fill down to the end of Column A. Every row with an entry now has its Row number in Column A. All other rows have 0 in Column A. Now define the datarange:

=OFFSET(Sheet1!$B$1,0,0,(MAX(Sheet1!$A:$A)),COUNTA(Sheet1!$B$1:$IV$1))

Of course if you have other data on your sheet, in rows not used in your data range, you will have trouble. This includes your PT, so make sure you do not put it on the same sheet your data is on!

Tried it on another printer (Xerox 470ST) and it printed fine. So it looks like a printer problem, which surprises me as the problem occurred with an HP LaserJet 5Si which normally is trouble free, and the spreadsheet is not very big.

Andrew:
The lines are visible on screen, and in print preview.
I tried borders round the cells, it didn’t solve the problem.
Lief:
Yes, cells 1-11 are gridded normally. Cell 12 which is about 25mm wide has lighter gridlines for 10mm from the left side, then blank. The heavier lins around subtotals make it as far as cell 14 then do the same thing. The vertical gridlines are normal.
There are no graphics on the sheet.
The top cell is merged, full width of the spreadsheet.
No the cells are not overfilled with data.
Legare I will try it out on another computer and printer.

Thanks everyone for your replies.

Assuming your fields are separated by spaces, this macro will do what you want. First make sure you have enough blank columns to receive the parsed addresses, then select all the addresses, then run the macro.

After you tell it how many fields you want for the State, postcode, etc., it starts at the end of each address and runs backwards until it reaches a space, puts this string in a new cell, then repeats the process until it has filled the number of fields chosen. It then puts whatever is left in the column next to the unparsed addresses.

Sub AddressParse()
‘
Do Until NumFields >= 1
NumFields = (InputBox(“How many fields AFTER the city name?”))
Loop

Set selrange = Selection
For Each Cel In selrange

gg = Cel.Value
Cel.Value = “=Trim(“”” & gg & “””)” ‘removes excess spaces

m = Val(NumFields)
p = m + 1
sl = Len(Cel.Value)
For n = sl To 1 Step -1
r = sl – n

If Mid(Cel.Value, n, 1) = ” ” Then
Cel.offset(0, p).Value = Mid(Cel.Value, n + 1, sl – n)
sl = n – 1
m = m – 1
p = p – 1
If m = 0 Or n = 1 Then
Cel.offset(0, p).Value = Mid(Cel.Value, 1, n – 1)
n = 1
End If
End If

Next n

Next Cel
End Sub

I can see a potential problem here as you are looking at a column in the body of your data, which might contain blank cells. CountA counts only the data containing cells, so your defined range might be some rows short of the whole database. (PT’s still work OK if there are some blank cells in the data).

The following, very similar, solution was given by DanielD Maramba in the old Lounge:

Datarange = Offset($A$1,0,0,CountA(Offset($A$1,0,0,9999,1)),5).

If you have more than 9999 rows your need to increase this figure.

The safest way to find the last row in a sheet is to go to the end row (65536 in XL97, 2000?) and then come up to the last used row above this, cannot see any reason why this could not be incorporated in the datarange definition.

It is quite possible to open a different .xlb file whilst XL is running, if you do this the toolbar/button set associated with that .xlb file will be displayed. This is quite useful if you often use a particular set of buttons for certain types of applications. If you close XL at this stage your normal .xlb will be overwritten, therefore make sure you have a copy of your this as Standard.xlb.

In XL97 there is more information on this topic under toolbars/About toolbars, and toolbars/multiple configurations.

Custom lists are also stored in .xlb files (plus something else that I cannot recall -this was discussed in the old lounge).

This is based on XL97 as I don’t have XL2000.

If your updated data are in the same range as your old data just click the Refresh button.
If they are in a different range, call up the PT Wizard, go back to the data source page, enter the new range, and click the finish button.
If you are just adding more data to the existing data you can automatically add this to your PT using the offset function in the data source page.

Select the cells you want to convert to Proper then run this macro:

Sub Proper()

For Each cel In Selection
gg = cel.Value
cel.Value = “=Proper(“”” & gg & “””)”

Next cel

End Sub

For an excellent alternative tweaking program see XTeq X-Setup in Software Finds.

There is a Paste Special/Values button via the Tools Menu, Customise/Commands/Edit, worth dragging onto a toolbar if you do a lot of pasting Values Only.

I read in another forum about a hardware problem where the fan was still running, but too slowly. Replacing it with a more powerful fan cured the problem.

This was discussed at length in the old Lounge, including how to set up Live Scrolling with the scroll bar (is this an option in W2000?). With LS you can see where you are getting to in your document, combine this with the click – shift/click method of selection.

Other options include a wheel mouse, and MouseImp (http://www.tv4studios.com[/url%5D) a great freebie that gives you live scrolling by holding down the right mouse button, saves having to go to the scroll bar to scroll. Also (in W97 at least) AutoScroll (Tools/Customise/Tools, drag the AutoScroll button onto a toolbar) which is a bit weird but give it a try.

You have to grab the edge of the column you want to move with the arrow rather than the white cross. Holding down Shift and dragging will then do what you want.

Great security feature, it will keep out hackers who cannot read English, cannot find the Ctrl/Alt/Del keys, or are too poorly coordinated to press them together.