November 29, 2004

Delete Blank Rows 3

Last night when I tried to post, the blog site was down for maintenance. My apologies to anyone who visited during that time.

Back in September I posted about deleting Blank Rows. Here is another way to it with array formulas.

Get the Cell Address
Enter this formula into same number of cells as your range and push to Ctrl,
Shift and Enter. (Braces at either end will appear automatically as in the picture below)

{=ADDRESS(SMALL(IF(B2:B14="","",ROW()),ROW(INDIRECT("1:"&COUNTA(B2:B14)))),2)}



This formula returns the row number for non-blank cells in descending order with the
SMALL function. The ADDRESS function does the rest. Adjust accordingly to get the right column or use this self -adjusting version.

{=ADDRESS(SMALL(IF(B2:B14="","",ROW()),ROW(INDIRECT("1:"&COUNTA(B2:B14)))),COLUMN(B2))}

Get the Values
Once you have the addresses, it's not to hard to get the values.

=IF(ISNA(C2),"",INDIRECT(C2))



Or you could just get the values from the start with this array formula.

{=INDEX(B2:B14,SMALL(IF(B2:B14="","",ROW()),ROW(INDIRECT("1:"&COUNTA(B2:B14))))-1)}



This version ajusts for using different rows.

{=INDEX(B2:B14,SMALL(IF(B2:B14="","",ROW()),ROW(INDIRECT("1:"&COUNTA(B2:B14))))-(ROW()-1))}

If you don't like those nasty #N/A errors, you might want to consider using something like this to chase them away. (Conditional Formatting can also be used)  

Posted by andrewe at 08:38

November 24, 2004

More Macros

Recently my friend and teacher Colo has been teaching me some very useful macros.

OnKeys
This enables you to use certain keys to trigger macros. For example, if I wanted to use the Space key, I can use OnKeys to designate that key to start a macro of my choice.

Here is an example.

Sub Auto_Open()
    Application.OnKey "{32}", "Macro1"
    Application.OnKey "{13}", "Macro2"
End Sub

Sub Auto_Close()
    Application.OnKey "{32}"
    Application.OnKey "{13}"
End Sub

Sub Macro1()
    MsgBox "Macro1 is running."
End Sub

Sub Macro2()
    MsgBox "Macro2 is running."
End Sub

In this case, the Space key (32) will run Macro1 and the Enter key (13) will run Macro2. Note the Auto_Close macro which sets the keys back to normal when the Workbook is closed. Also be aware that other files will also be affected until the original files is closed or canceled.

Colo also made a useful tool called the Keycode Checker which tells you the key number of the key that is pressed. Please download his example file here. (Not all of keys are included but there is also a list you can refer to the Help Files of the Visual Basic Editor)



Zoom Setting
This is another handy piece of code that will size a Workbook to fit screens of different sizes.

Private Sub Workbook_Open()
    Range("A1:AL25").Select
    ActiveWindow.Zoom = True
End Sub

This enables you to ensure that all of a range you choose will fit as planned.

Scroll Area
This seems very similar to Worksheet Protection.

Private Sub Workbook_Open()
    Worksheets("Sheet1").ScrollArea = "A1:J10"
End Sub

By setting the scroll area, you only allow access to cells within that range.  
Posted by andrewe at 23:47

November 21, 2004

Random Numbers 4

When I was making my Battleships game, I used random numbers to position the ships in different locations each time. As I didn't want to use any special add-ins, this prompted me to think about getting random numbers by just using regular functions.

Sequential Non-Repeating Random Numbers
This was quite straightforward. All that is needed is for the first number in a range to be random then add a simple formula to get the sequence.

For numbers 1 to 10, I enter this formula in let's say cell B2,

=INT(RAND()*10)+1

Then in cell B3, I enter this formula and drag down to B11 (a range of 10 cells)

=IF(B2=10,1,B2+1)



This will give you a series sequential numbers but from constantly changing starting points (Odds are you may get the same series of numbers more than once, use extra random formulas if you want to stop this happening as much as possible)

Non-Sequential Non-Repeating Random Numbers
This was a little harder but I remembered seeing some VBA code once before that gave me the following idea.

This time in cell B2, I enter 1234567890, then in cell C2 this formula and drag down to cell C11,

=MID(B2,INT(RAND()*LEN(B2))+1,1)

and in cell B3, this formula and drag down to B11,

=SUBSTITUTE(B2,C2,"")



There are 2 things happening. First, the MID, RAND and LEN formulas in Column C selects one number from the range of numbers to the left in Column B. And, the SUBSTITUTE formula directly below in Column B deletes this number which in turn decreases the range of numbers to be chosen from.

Pretty neat. And if you want bigger numbers you can just use the same range C2 to C11 again and again like this formula which will give you numbers from 0 to 999.

In cell D2,

=C2*100+INDEX($C$2:$C$11,INT(RAND()*10)+1)*10+INDEX($C$2:$C$11,INT(RAND()*10)+1)

and drag down to cell D11.



Not the simplest but it works.  
Posted by andrewe at 19:55

November 17, 2004

Battleships

This is my second game and one I liked to play when I was a kid.



Click here to download Battleships. It's fun ;-)  
Posted by andrewe at 21:19

November 14, 2004

More Custom Functions

Following up from where I left off last time, here are some Custom Functions to show whether a cell or range is red. (The Color Index for Red = 3)

IsRed
The following function will show either TRUE or FALSE using If and Then.

Function IsRed(cell As Range) As Boolean
    If cell.Interior.ColorIndex = 3 Then IsRed = True
End Function



CountRed
TRUE and FALSE may be fine but how would you go about counting red cells?

For me, the simple way (and the only way I knew at the time) was to use a double negative as below. (This gives TRUE the value of 1 and FALSE the value of 0)



This way works for single cells, but if you look closely you will see that it will not work properly for ranges with mulitple cells. Colo showed me a better way to do it by using by a loop. (Thanks Colo!)

Function CountRed(cell As Range) As Long
    Dim c As Range
    For Each c In cell
        If c.Interior.ColorIndex = 3 Then CountRed = CountRed + 1
    Next
End Function



Note these formulas will not work with Conditional Formatting, but then I suppose it wouldn't be too difficult to adapt the formula used in the Conditional Formatting to make the cell or range red in the first place.  
Posted by andrewe at 22:16

November 10, 2004

Hyperlinks

This is the second time I've written about Hyperlinks.

The first time was in my post, Quick Navigation, Part 1 which was about inserting Hyperlinks from the Insert Hyperlink dialog box.

It works quite well, but it does have some limitations as the destination address is fixed. As an alternative, you can use the HYPERLINK function to link to an address that is determined by a formula.

First, let's look at this function which gives us the last cell address in a column, (in this case Column C)

=ADDRESS(MATCH(9.99999999999999E+307,C:C),3)



Note that the number 3 is used to refer to the third column.

Now here is a HYPERLINK formula to go there automatically.

=HYPERLINK("#Sheet1!"&ADDRESS(MATCH(9.99999999999999E+307,C:C),3),"Last Row")



The text "Last Row" can be changed to anything you prefer.

Speaking of text, the above formulas will only work with numbers, but here is alternative formula that can be used with text as below.

=HYPERLINK("#Sheet1!"&ADDRESS(MATCH(REPT("z",255),C:C),3),"Last Row")



Note: The above HYPERLINK formulas should work, but if you get stuck please refer to your Help files for more details.  
Posted by andrewe at 18:40

November 06, 2004

Custom Functions

Recently I've taken the plunge and have been trying to learn more about VBA. Admittedly I still don't know much but here are some custom functions I have found on the forums and thought are very handy.

GetFormat

The code for GetFormat is,

Function GetFormat(Cell As Range) As String
    GetFormat = Cell.NumberFormat
End Function



Note: You can also use the CELL function as in =CELL("format",A1)to do much the same thing, but it seems to be limited to what formats it can return. See the Help files for more details.

GetFormula

Another useful custom function,

Function GetFormula(Cell as Range) as String
    GetFormula = Cell.Formula
End Function



As you can see, the code for both custom functions is quite similar. With some experimentation, even someone as inexperienced as I can put together a few simple custom functions (sometimes with a bit of help from my friends :-) )  
Posted by andrewe at 23:21

November 03, 2004

Symbolism

I was asked the other day about using Greek symbols in Excel. (Hi Valerie!) Greek symbols are particularly useful as they pop up all the time in Mathematics and stuff.

There are a few ways to do this. One of the easiest ways is to select Insert, Symbol to bring up the Symbol dialog box.



Unfortunately earlier versions of Excel don't have this feature, but I used to access it on Word instead and then copy and paste symbols into Excel. (Sometimes dumb is smart)

Another way is to use the ALT key and then type in combinations of 4 digits with the numeric keypad. This method runs into problems if you are using a laptop :-(

Yet another way is to use the Windows Character Map which seems to show each and every font you have installed. You can find it on the Start menu (Start, Programs, Accessories, System Tools)

It's a trifle unwieldy but you can copy characters to the clipboard and then paste it where you like. (I'd include a picture but my system is Japanese Windows so it wouldn't be much use unless you can read what it says)

Then we have the CHAR function. This function works quite well but Greek symbols were not included. It also seems that the characters available change according to your system's language. Fortunately some of the more common characters appear to remain the same which is quite useful when working with formulas.

Last but not least, I found an interesting way to get the aforementioned Greek symbols by using the Symbol font (no surprise?)

Here's a picture of small and capital letters in a regular font, with the equivalents in Symbol font side by side.



Not so useful to some, but in my case I often use the "diameter" symbol at work and so now I can just use a small f, then use the mouse to change to Symbol in italics where it is needed to get something like this.



It also works with other software like Word and Outlook Express.  
Posted by andrewe at 17:15