Add Multiple Blank Rows In Microsoft Excel

Wow!  Long time without a post.

Anyway, I can’t believe that I’ve never realised how hard it is in the older version of Microsoft Excel to insert multiple blank rows.

I searched Google and found a few sites saying press F4 or buy our tools so I wrote  quick macro.

The macro asks how many rows you want to insert and then inserts that many rows ABOVE the current cursor position. (If you don’t specify a number of rows it defaults to 2).

I’ve tried it a few times and it seems to work okay.

If this may be useful to you perhaps give it a go!

Sub Macro1()
'
    Application.CutCopyMode = False
    
    Dim strHowManyRows As String
    Dim NumberOfRowsToInsert As Integer
    Dim c As Integer ' Just a counter


    strHowManyRows = InputBox(Prompt:="How many rows should I insert? (These rows will be inserted ABOVE the currently selected row)", _
          Title:="HOW MANY ROWS?", Default:="2")

          

        If strHowManyRows = "" Or _
           strHowManyRows = vbNullString Then
            
            
           NumberOfRowsToInsert = 2
        Else
    
           NumberOfRowsToInsert = Val(strHowManyRows)
    
        End If
        
        
    For c = 1 To NumberOfRowsToInsert
        Selection.Insert Shift:=xlDown
    Next c

End Sub
Advertisements

A film review

On the 28th of October 2012 I went to York Vue to see Hotel Transylvania I thought it was awesome! My family thought it was, well wait a minute, my sister has two things she thought it was: fantastico and excellent.

Stuart Bell Swim School

Just a quick shout out for the Stuart Bell Swim School. (Teaching children to swim in York, UK since 1991). Take a look at http://www.stuartbellswimschool.co.uk.

A CSS and Javascript Calculator

I was doodling this afternoon on the computer and created this calculator.

It’s created using CSS and Javascript.

The CSS is drawn from http://css-tricks.com/examples/ButtonMaker/# and the javascript from http://www.code-sucks.com/code/javascript/template.php?tutorial=calculator.php.

I can’t embed the calendar on here but it looks like this:

Calendar Using Javascript and CSS

It’s nothing particularly clever but should allow you to put a simple calculator on a web page. You can change the CSS to change the gradient colour of the buttons.

Please post any comments on how you could improve the code or functionallity of the calculator.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html>
<head>

<style>

.button {
 cursor: pointer;
 margin: 10px;
 float: left;
 width: 25px;
 text-align: center;
 border-top: 1px solid #96d1f8;
 background: #65a9d7;
 background: -webkit-gradient(linear, left top, left bottom, from(#3e779d), to(#65a9d7));
 background: -moz-linear-gradient(top, #3e779d, #65a9d7);
 padding: 5px 10px;
 -webkit-border-radius: 8px;
 -moz-border-radius: 8px;
 border-radius: 8px;
 -webkit-box-shadow: rgba(0,0,0,1) 0 1px 0;
 -moz-box-shadow: rgba(0,0,0,1) 0 1px 0;
 box-shadow: rgba(0,0,0,1) 0 1px 0;
 text-shadow: rgba(0,0,0,.4) 0 1px 0;
 color: white;
 font-size: 14px;
 font-family: Helvetica, Arial, Sans-Serif;
 text-decoration: none;
 vertical-align: middle;
 }
.button:hover {
 border-top-color: #28597a;
 background: #28597a;
 color: #ccc;
 }
.button:active {
 border-top-color: #1b435e;
 background: #1b435e;
 }

.button_big {
 cursor: pointer;
 margin: 10px;
 float: left;
 width: 95px;
 text-align: center;
 border-top: 1px solid #96d1f8;
 background: #65a9d7;
 background: -webkit-gradient(linear, left top, left bottom, from(#3e779d), to(#65a9d7));
 background: -moz-linear-gradient(top, #3e779d, #65a9d7);
 padding: 5px 10px;
 -webkit-border-radius: 8px;
 -moz-border-radius: 8px;
 border-radius: 8px;
 -webkit-box-shadow: rgba(0,0,0,1) 0 1px 0;
 -moz-box-shadow: rgba(0,0,0,1) 0 1px 0;
 box-shadow: rgba(0,0,0,1) 0 1px 0;
 text-shadow: rgba(0,0,0,.4) 0 1px 0;
 color: white;
 font-size: 14px;
 font-family: Helvetica, Arial, Sans-Serif;
 text-decoration: none;
 vertical-align: middle;
 }
.button_big:hover {
 border-top-color: #28597a;
 background: #28597a;
 color: #ccc;
 }
.button_big:active {
 border-top-color: #1b435e;
 background: #1b435e;
 }

</style>

</head>

<body>

<table border="1" style="border-collapse: collapse" bgcolor="#999999" cellpadding="0" bordercolor="#808080">
 <tr>
 <td colspan="4">
 <form name="calculator">
 <input type="text" disabled id="total" style="text-align: right; background: white; width:98%; font-family:Courier New; font-size:18pt; font-weight:bold" size="1" name="total">
 </form>

 </td>
 </tr>
 <tr>
 <td><div onclick="calculator.total.value += '7'">7</div></td>
 <td><div onclick="calculator.total.value += '8'">8</div></td>
 <td><div onclick="calculator.total.value += '9'">9</div></td>
 <td><div onclick="calculator.total.value += '/'">/</div></td>

 </tr>
 <tr>
 <td><div onclick="calculator.total.value += '4'">4</div></td>
 <td><div onclick="calculator.total.value += '5'">5</div></td>
 <td><div onclick="calculator.total.value += '6'">6</div></td>
 <td><div onclick="calculator.total.value += '*'">*</div></td>

 </tr>
 <tr>
 <td><div onclick="calculator.total.value += '1'">1</div></td>
 <td><div onclick="calculator.total.value += '2'">2</div></td>
 <td><div onclick="calculator.total.value += '3'">3</div></td>
 <td><div onclick="calculator.total.value += '-'">-</div></td>

 </tr>
 <tr>
 <td colspan="2" align="center"><div onclick="calculator.total.value += '0'">0</div></td>
 <td><div onclick="calculator.total.value += '.'">.</div></td>
 <td><div onclick="calculator.total.value += '+'">+</div></td>

 </tr>
 <tr>
 <td><div onclick="calculator.total.value = ''">C</div></td>
 <td>&nbsp;</td>
 <td colspan="2"><div onclick="calculator.total.value = eval(calculator.total.value)">=</div></td>

 </tr>

</table>

</body>

Access 2007: Refering To A Control On A Subform Within A Form On A Tab

Wow! typing that heading is more complicated that solving the problem!

I have a main for that contains a number of tabs (let’s call the form “frm_MainForm”). The tab is called “tab_MyTab” (but this doesn’t really matter – see below!).

One one of those tabs contains another form (let’s call it “frm_FormOnTab”).

This form then contains a subform (let’s call this one “frm_subformOnForm”) which in turn contains a control for which I want to retrieve the value (let’s call the control “txt_MyControl”).

So, to summarise, we have:

frm_MainForm
tab_MyTab
frm_FormOnTab
frm_subformOnForm
txt_MyControl

How do we reference the value contained in txt_MyControl?

The syntax is:

=[Forms]![frm_MainForm]![frm_FormOnTab]![frm_subformOnForm].[Form]![txt_MyControl]

SQL Server – Enter Value from Keyboard

Here’s another of those things I forget if I don’t use SQL Server for a while.

To enter a value of <NULL> into a field in SQL Server just type:
[Ctrl] + 0

(Hold down the Ctrl key and press the zero key).

Access VBA – Change the Name of a Field in an Existing Table

I’ve just been importing some records from Excel to Access using VBA.

By default the names of the fields of the imported records are “F1”, “F2”, “F3” etc.

To make things a little clearer I wanted to rename these imported fields to more meaningful names.

Here’s how to do it:

CurrentDb().TableDefs(TableName).Fields("OldName").Name = "NewName"

where:
TableName is the name of the table in which we want to change the names
“OldName” is the old name of the field
“NewName” is the new name we want to use for this field


Blog Stats

  • 223,208 hits

Counter

ClustrMap