Selecting only visible cells in Excel

I found a neat solution to a problem I have manually been working around for quite sometime.

If you have hidden any rows or columns in Excel and want to copy the remaining data only, or if you have made use of the outlining tool and have created subtotals in your document and you only want to copy those subtotals then you can do so by following these steps;-

1. Select the data you want to copy
2. Press Ctrl+G to bring up the "Go To" window
3. In the bottom left corner click the Special button which will bring up the "Go To Special" window
4. From this window select "Visible cells only" 
5. Click OK

Now copy and paste the selection as you normally would.

Connect Excel to Microsoft SQL Server and query Database

Open Microsoft Visual Basic for Applications (Alt+F11)

Click Tools – References

Add “Microsoft ActiveX Data Objects 2.7 Library”

Create a new module (I tend to keep all my SQL code in a module of its own e.g. mod_SQL)

Then create a new function or sub routine

Function myFunctionName(ByVal s_myString As String) As Integer

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

'Create the connection string.

sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _ 

"Initial Catalog=DBName;" & _

"User Id=Username; Password=Password"

'Create the Connection and Recordset objects.

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

'Open the connection and execute. 

conn.Open sConnString

Set rs = conn.Execute("SQL Statement")

'Check we have data.

If Not rs.EOF Then

myFunctionName = rs("Return Value")

' Close the recordset

rs.Close

Else

myFunctionName= -1

End If

'Clean up

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

End Function

Word / Office 2013 has stopped working

Annoyingly if you look around the web for Word / Office crashing on start up or "has stopped working" you will find thousands of posts all recommending the same thing; start word in safe mode and then disable COM Addins. 

After trouble shooting the issue for 3 hours and trying a variety of things I finally managed to resolve my problem. It seems my users issue was to do with their USB docking station (and the version of the Display link Adapter software they had installed)

I guess I could try upgrading the version of the software, but the user works in a different country so I don't want start uninstalling anything that may cause me to be disconnected. To get them up and running I was able to disable hardware acceleration for office, to do this

Run Regedit
Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common
Create a new Key called Graphics
Under the new key create a new DWORD called DisableHardwareAcceleration
Set the DWORD value to 1

Excel VBA adding a comment to a cell

Code below can be used to add comments to a cell. I find this useful for providing errors and feedback to the end user.


Dim o_Range As Range

Set o_Range = Sheet1.Range("A1)

o_Range.ClearComments
   
o_Range.AddComment "Test Comment"

US / UK Date format reverse in Excel

This problem has been bugging me for 3 days now; I am retrieving a date time from SQL. I have verified that the date is in my required format dd-mm-yy, and I have tried a few different ways of selecting the date just to be sure DATEPART(day, fieldname) , DATEPART(month, fieldname), I have even tried dd-MMM-yy.

If I display the date on a form or in a messagebox it displays fine, the moment I put the date value into an Excel cell it flips the day and month around. Originally I was going to work around the issue by prefixing all my dates with a single quote ‘ so that Excel treats them as being strings, however the end user wants to sort on some of the date values and it won’t work quite right as a string.

After banging my head against the wall for 3 days I finally came across the DateValue(date) function! Figured I’d share the solution to this frustrating little problem.

Excel 2010 .xlsm File hangs when opening

I've been writing some VBA macros to improve an Excel workbooks functionality. I was modifying a Sub Routine and just in case I made a mistake and need to revert back to it I made a copy and appended _backup to its name. As it happens I decided I preferred the original way the sub routine worked so I restored it (deleted the original routine) and then renamed the _backup. Turns out I had accidentally copied the routine twice (VBA didn't warn me of this). Thinking everything was fine I saved and closed my workbook, when I came back to it a few hours later I was unable to open it, Excel 2010 just sat at 100% without allow me into the workbook.

I found a few suggestions online to fix this and none worked, so here's what I did to get mine working.

Change the extension of the file from .xlsm to .xls and then open the file. I got prompted that there was a file type mismatch (or something similar) and it also gave me a complication error, something along the lines of problem with sub routine. As soon as I saw the sub routine error I knew where my coding problem was, corrected it and then saved the file back as .xlsm.

I hope this helps someone out, had me in a panic for a few moments.

Excel cannot complete this task with avaliable resources.

 

 

If You've ever come across the above error when trying to open an Excel workbook ("Excel cannot complete this task with available resources. Choose less data or close other applications") then you can resolve it by increasing its priority in Windows.

 

 

With Excel still running, open task manager and locate the Excel.exe process, right click on it and navigate to priority, normally it should be set to normal, if you increase this to "Above Normal" you should be able to open your problematic workbook. I am not sure of the underlying cause of this and I've only seen this problem when running Excel 2010, it is defiantly not a hardware/resource issue.

auto increment (auto_increment) in Excel with VBA (auto generate line numbers)

I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)

I knocked up a quick macro that seems to do the job for the user

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

     

    If Target.Column > 1 And Target.Column <= 7 Then

   

        If Target.Row - 1 > 0 Then

            Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1

        End If

      

    End If

End Sub

Excel Tips

Recently I’ve been doing a lot of working with Excel and I’ve really pushed myself to solve some problems that have bugged me for a number of years.

I wanted a way to convert between column letters and a numerical value. As you may be aware you can reference a cell via its row number and the column letter, but you can also reference it via row number and column number e.g. A=1, B=2 and C=3

It’s useful sometimes (when programming especially) to switch between the methods you use. Not wanting to type out (in this case) 153 strings e.g. A,B,C,D ... EZ, I found the following formula which can be copied into A1 and downwards and it will give you the running values

=SUBSTITUTE(ADDRESS(1, ROW(), 4), "1", "")

Very useful.

The second formula (actually function) I’ve come across is 

=SUMIF(range,criteria,sum_range)

Usage: Range is the range of cells you want to query on e.g. Place, Sex, Unit of measure, Criteria is the what records you want it to match on e.g. London, Male, KG. Sum Range is the range of cells you want it to total up.

Very simple, yet very powerful

PHPBB3 Tables and Excel

One of the forums I currently host and manage makes use of PHPBB3, the user’s post a lot of different tables (league standings e.t.c.) so being able to post tables is a must so I added some BB codes for [table], [tr] and [td] and they can now happily post all the tables they like.

The source data for these tables is held in excel, and having to upload and update 3 different tables each week is a little tiresome so I developed a little VBA Macro that will convert a selection to PHPBB3 Table code.

Throw a button on the toolbar and link it to the macro and job done!

---

Private Sub CommandButtonClose_Click()
    'closes the form
    End
End Sub

---

Private Sub CommandButtonCopy_Click()
    'Copies the content of the TextBoxTable
    Dim ansDataO As DataObject
    Set ansDataO = New DataObject
   
    ansDataO.SetText TextBoxTable.Text
    ansDataO.PutInClipboard
End Sub

---

Private Sub UserForm_Initialize()
    Dim i_ColumnCount As Integer
    Dim i_RowCount As Integer
    Dim i_ColumnLoop As Integer
    Dim i_RowLoop As Integer
   
    'Gets the number of columns / rows in our selection
    i_ColumnCount = Selection.Columns.Count
    i_RowCount = Selection.Rows.Count
 
    'stores the phpbb code
    TextBoxTable.Text = "[table]"
   
    'loops through each row in our selection
    For i_RowLoop = 1 To i_RowCount
        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[tr]"
           
        'for each row we loop, now loop through the column
        For i_ColumnLoop = 1 To i_ColumnCount

            TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[td]"
           
            TextBoxTable.Text = TextBoxTable.Text & Selection.CurrentRegion.Cells(i_RowLoop, i_ColumnLoop).Value
                   
            TextBoxTable.Text = TextBoxTable.Text & "[/td]"
        Next

        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/tr]"
    Next
       
    TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/table]"

End Sub

---