Is there a way to set to Excel print gridlines all the time?
Thanks, Cap!
StG
Not all of the time.
If you click on
File > Page Setup
and then select the Sheet tab, under the Print section is a check box for Gridlines.
This option will print the gridlines when you print the Worksheet you are currently on. When you save the Spreadsheet, this option will also be saved so when you open the sheet up again it will continue to print the Gridlines.
This option only works for the Worksheet you are currently on though and will have to be set for all Worksheets within the Spreadsheet that require this.
3 questions (to start :)):
- I’ve created a marcro to reorganize and format a range of cells, but each time I do this, the number of rows is likely to change. When I do it manually – I double-click on the bottom or right active cell borders to select all columns to the right or rows below until an empty cell is encountered. But the macro record function decides I’m moving a fixed number of rows down. If I repeat the macro on a range with more rows, it only selects as many rows as there were in the original range that I used to record the macro.
So I need some code to edit the macro, so I can tell if to select down as far as there are rows with content.
- I have a column with text values, or blank cells. I want a macro to:
a: move to the first cell with a value.
b: test the cell beneath for contents.
c: if the cell beneath is empty, just skip on down to the next cell with a value.
d: if the cell beneath also has a value, I want the contents to be cut out of that cell, and appended to the contents of the top cell, with a line break with a line break between the original and appended contents.
e: I want this appending to reiterate until the next blank cell is found, and then it can skip on down to the next cell with contents below the blank one, and go again. It is possible that there may be as many as 6 cells that would need to be appended to each top cell.
- Is it possible to “lock” 2 or three rows together so they do not get separated in a sort, regardless of the sort criteria? Merging the rows is not an option because of losing data in the merge, unless you know a way around that as well.
Actually, just stating problem 2 has shown me another catch to a macro like this, Each cell that I cut the contents out of beneath the first cell, will leave a blank cell beneath, so the macro needs to be able to know whether the cell was originally blank, or was *made * into a blank by the macro. I guess that would require some kind of counting function that would be reset for each group of cells.
Thank you in advance for any help you can provide.
Is there any way to do a broken Y axis when graphing in Excel? You know, when you’re graphing two different things on the same graph and one goes from 1 to 5 and the other goes from 1 to 60000, so if you graph it on an unbroken axis the smaller values look meaningless.
I found a way online that seemed, well, ridiculously complicated. Shouldn’t there be some easy way to do this? Is there one.
Boyo Jim
- Right Click on the command buttons and put a tick against the Visual Basic option so it is shown on your menu. The third button on this set of commands is the Relative/Absolute button. Set this to relative when recording your macro and it should follow your selection method rather than the selection itself.
Alternatively, when selecting the last cell in your range, do it in the following manner instead.
Click on
Edit > Go To > Special
and pick the Last Cell option. This will select the Last Cell used in your Workbook.
2) I’ll have a play around with this and get back to you if I find an answer.
- I’m fairly certain the answer to this is no as you are explicitly telling Excel to rearrange the data. Again, I’ll have a play around.
Boyo Jim
Try this macro but beware the following!
-
It assumes your data is in Column A. If this isn’t the case, alter the 2nd line of code to show the Column you want to work on.
-
It looks for a situation where there are 2 consecutive empty cells below it and assumes that this is the end of the data.
-
It gets round the problem you mentioned of remembering if it pulled data from a cell by deleting the row it pulled it from. This also makes the data look neater but could cause problems if other Columns have data in them.
Sub ReOrder()
Range(“A1”).Select
Do Until Selection.Offset(2, 0).Value = “” And Selection.Offset(1, 0).Value = “”
If Selection.Offset(1, 0).Value = “” Then
Selection.End(xlDown).Select
Else
Selection.Value = Selection.Value & Chr(10) & Selection.Offset(1, 0).Value
Selection.Offset(1, 0).EntireRow.Delete
End If
Loop
Range(“A1”).Select
End Sub
Let me know how this works out for you.
TestKeys, I thinmk this may work. What you need to do is make changes to your default workbook. Do these steps:
-
Start with a new workbook.
-
Give it all the options you want it to have. In your case default printing with gridlines. But you can also change the number of worksheets, column width, headers, footers, etc.
-
Go to file save as and select Template (*.xlt) from the “Save as” type box.
-
Save it as book.xlt in the \XLStart folder. It is possibly in something like C:\Program Files\Microsoft Office\Offie Folder.
Now every “New” file you start with will be from this template. You can change it directly anytime as long as it is saved as book.xlt in the correct folder. Also, if you delete that file it will go back to using the standard default workbook settings.
Is there a way create a formula in a cell with abstract variables?
For example,
say the formula in cell A1 is =“Some Funky Cell” * 5
Here “Some Funky Cell” would refer to a cell name that I would enter in something like cell B1.
I hope that is clear.
On another note, do you have to know where I can find a listing of all the functions used to program macros in OpenOffice Calc?
Enter the number 7 into cell A1
Click on - Insert > Name > Define
Make sure that the ‘Refers to’ label shows cell A1 and name this “SomeFunkyCell” (Excel will not allow spaces to be used).
In cell B1 type =SomeFunkyCell*5
and this will give you the answer 35.
Please explain the V-lookup and similar functions in short, eas-to-understand terms and instructions.
StG
Here’s the spreadsheet I’m working with, which is tracking wins and losses in online mafia games. What I’d like to do is be able to not count games that didn’t finish (marked with a D on row 3) into each person’s WL percentage. So, for their Total Games (Column C), I need to be able to only count games that are not a ~ or an R in their own row (which it already does), as well as disregard games that are marked with D in row C.
I can’t figure out how to code it. Any ideas?
JSexton
Type this into cell C4
=SUM(IF(E$3:AT$3<>“D”,IF(E4:AT4<>"~",1,0),0))-SUM(IF(E$3:AT$3<>“D”,IF(E4:AT4=“R”,1,0),0))
and then before you hit enter type CTRL-SHIFT-ENTER. This should make it into an array by putting {} around the formula. You can now copy the formula down to the bottom. I think this works.
Awesome! It certainly does seem to work. Would you mind breaking down those bits for me so I understand why? 
Never mind, I did some playing and got it sorted. I even cannibalized it to make column B much less complex. Thanks so much!
I’ll try to the best of my ability. What you are doing with the CTRL-SHIFT=ENTER is creting an array formula, which basically assesses each formula into an array. TO see what I mean in the formual in C4 select the first IF statement (everything within the first set of parenthesis) and hit F9. What you should see now is a series of ones and zeroes in the curly brackets. So what you have done is evaluated the if formula sort of like a loop statement for each occurence and created an array of ones and zeroes to be evaluated by the SUM function. We do this twice, once to count all the non-D and non-"~" occurences and then we need to also subtract out the “R” occurences, being sure not to re-subtract out the D occurences again.
I’ve got a user who’s Excel (MS Office Excel 2003 on WinXP SP2) is “going crazy” (her words) with page breaks. Individual worksheets within a spreadsheet suddenly manifest page breaks surrounding every cell in the worksheet. She reports at least one other user has the same problem, it doesn’t happen all the time, and she says it happens with a handful of files. I googled for “Excle Going Crazy” but didn’t come up with anything.
Thoughts?
intention, a non-boardmember friend would like to ask you some questions. May I ask you to email me to get connected with her? My email is in my profile (and yours is not).
When using a PMT, FV, or PV function, is there some way I can incoporatea PMT argument that increases? Say I want to calculate the PV of a loan whose payments increased 3% every period?
Okay, this ain’t nearly so esoteric, but…
Is there any way to edit a cell’s contents without using the mouse?
(ie; I’ve just used the arrow keys to navigate to the cell I want to modify. Can I just get a cursor at the end of the string without reaching for the cussed mouse? Or is this too much to ask?)
Thanks!