Most frequently used piece of VBA

[schielrn]

I wish Paste Special included an option to choose whether or not to move references relatively or not...

This is one of the 4 or 5 options that I use ASAP utilities for daily. I couldn't function properly without having the ASAP utlities tool, even though before I knew about the tool, I had quite a few of the options created in my own macros.

 

[AngelJ]

I can't think of anything that I use repeatedly in my programming, but a lot of reports I use export to Excel with the dates stored as text. So I find myself using this in the Immediate Window often:

Selection = Selection.Value

 

[schielrn]

I can't think of anything that I use repeatedly in my programming, but a lot of reports I use export to Excel with the dates stored as text. So I find myself using this in the Immediate Window often:

Selection = Selection.Value

Text to Columns also works very well for this. Click text to columns and then Finish and you are done.

 

[AngelJ]

Text to Columns also works very well for this. Click text to columns and then Finish and you are done.

Thanks, didn't know about that!

 

[schielrn]

Thanks, didn't know about that!

Only thing is your vba approach will work with multiple columns selected, where as text to columns would not.

 

[Darren Bartrup]

I use this a lot:

Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range


    Dim lLastCol As Long, lLastRow As Long
    
    On Error Resume Next
    
    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = Col
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If
        
        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1
        
        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0
    
End Function

After that it's code for creating folders, getting all files of a certain type, and then it's .... I don't know, I've got hundreds of .bas files saved that I can just drag and drop into projects. Just need to index them and give them a better naming convention - now did I call that file GetLastCell, FindLastCell or just LastCell?

 

[xenou]

I like this one for putting files on my desktop (typically, some kind of temporary file):

Dim myDesktopFolder as string
myDesktopFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop")

However, my desktop has very little on it. That may be unusual!

Some routines I use every day are:
Putting light grey borders around cells
Setting my default fill color for "input cells"
Cleaning up Access import data (removing the wrap text option that comes with it)
Custom keyboard shorts: hard copying text, unfiltering filtered data, backing up a file, toggle manual/auto calculation, toggle show grid lines, toggle cursor direction after enter/tab.

Other generic routines are on my right click menu (which is heavily customized, as it my ribbon and QAT):
http://www.mrexcel.com/forum/excel-...izing-excel-right-click-menu-still-works.html
As a result of the keyboard shorts, ribbon customizations, and QAT I use Excel with the Ribbon minimized and don't actually have to use the ribbon very much except when designing new spreadsheets or building charts.

Don't we have a thread like this somewhere else already?

 

[Jon von der Heyden]

Setting my default fill color for "input cells"

Do you use custom styles? I like Styles for the formatting of different classes of ranges.

Don't we have a thread like this somewhere else already?

Yep! At least a couple if I recall - but I hate searching the forum with the new forum search.

 

[xenou]

I don't use Styles per se, but I do have an input cell style that I use - essentially, a background that I use exclusively for cells that require manual input of data, as opposed to calculated results or values that are determined programmatically.

Another nice trick I found described here (disabling the blasted help dialog from popping up unintentionally):
http://www.mrexcel.com/forum/excel-questions/723709-always-hitting-f1-when-i-mean-hit-f2-print.html

 

[Jon von der Heyden]

Another nice trick I found described here (disabling the blasted help dialog from popping up unintentionally):
http://www.mrexcel.com/forum/excel-questions/723709-always-hitting-f1-when-i-mean-hit-f2-print.html

Good idea! I don't tend to hit it accidentally too often but what does annoy me is when I do something that produces an error message and the default button is the help button! Circular references for instance - hit ENTER to dismiss the message and it actually loads the help. That really annoys me!