# Most frequently used piece of VBA



## Lewiy (Feb 10, 2014)

Hi Everyone!

It's been a while since I frequented these parts, but as I was writing some code today, it suddenly struck me that there are a few lines or chunks which I use almost every time I'm working with VBA, which I can type out with my eyes closed whilst having an entirely unrelated conversation with a colleague (yeah, they wonder why I look like I'm asleep whilst talking to them)!  So that got me wondering about what other people find they use ALL the time, be it VBA or perhaps specific combinations of functions in formulas.

To kick things off, my number one most frequently used chunk of VBA is for looping through all the rows in a worksheet:


```
Dim Limit As Long
Dim r As Long
With Sheets("SheetName")
    Limit = .Cells(.Rows.Count, 1).End(xlUp).Row
    For r = 2 To Limit
        'Do something
    Next r
End With
```


----------



## Jonmo1 (Feb 10, 2014)

Deleting blank rows

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


----------



## RobMatthews (Feb 10, 2014)

Jonmo1 said:


> Deleting blank rows
> 
> Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete



Nice! See, this is why I come here...

Edit: Actually, now I see that this only checks the first column before deleting the row...? Whereas my code checks for anything at all in any cell in the row.  Any way i could optimise this do you think?

It'd be a bit quicker than mine:


```
Sub RemoveBlankRows()
Dim LastRow As Integer
Dim LastCol As Integer
Dim CurrentRow As Integer
Dim CurrentCol As Integer
Dim myrange1 As Range
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
Set myrange1 = Range(Cells(1, 1), Cells(LastRow, LastCol))
For CurrentRow = 2 To LastRow
    If CurrentRow > LastRow Then Exit For
    For CurrentCol = 1 To LastCol
        If Len(Cells(CurrentRow, CurrentCol).Value) > 0 Then
            Exit For
        End If
    Next
    If CurrentCol = LastCol + 1 Then
        Rows(CurrentRow).Delete Shift:=xlShiftUp
        CurrentRow = CurrentRow - 1
        LastRow = LastRow - 1
    End If
Next
End Sub
```


----------



## SydneyGeek (Feb 11, 2014)

You can use something like this instead:

```
Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
```

Denis


----------



## Jon von der Heyden (Feb 11, 2014)

And generally for deleting rows based on condition (not blanks) I prefer to use the autofilter method instead of looping.  That way you only need to call the delete method once.


----------



## SydneyGeek (Feb 11, 2014)

Yep, filtering is a great way to avoid looping. Just remember to set calculation to manual before you filter or redisplay the rows. Much better performance.

Denis


----------



## Rick Rothstein (Feb 11, 2014)

I find myself almost always typing a line of code similar to this (with the column varying sometimes)...

LastRow = Cells(Rows.Count, "A").End(xlUp).Row


----------



## Richard Schollar (Feb 17, 2014)

I often find myself having a set of values (more than 2) that I need to filter a large dataset on.  So I frequently make use off the Immediate Window with two lines of code:


```
'select the values that will be used to filter then:
a = Application.Transpose(Selection.Value)  'populate a 1D array with the values to filter on
' go back to Excel, switch to dataset to be filtered
[A1].Autofilter Field:=1, Criteria1:=a, Operator:=xlFilterValues   '====this does the filtering.  Amend ranges/fields to suit
```


----------



## ZAX (Feb 19, 2014)

I find repeating this a lot to work with data pairs like:

Example   1
Example   1
Ex           2
Ex           2
Ex           2
e.g          3

```
For Each Cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If Cell.Value <> Cell.Offset(1, 0).Value Then
        'Code here to work with the continuous data parts
    End If
Next
```
ZAX


----------



## Jon von der Heyden (Mar 2, 2014)

Not the most frequently used overall, but frequently used in a current project...

I am rebuilding parts of a model and I frequently have to copy areas of formulas to other areas in the model.  The formulas use relative referencing so I have a quick cheat for copying them without having the references move...  I don't want to change all references in formulae to absolute because I may tweak them and want to copy the formula down/across...

1. Select the range you wish to copy
2. In the immediate window:

```
v=selection.formula
```
3. Hit ENTER
4. Select the range where you want to paste
5. In the immediate window:

```
selection=v
```
6. Hit ENTER

Nothing fancy - but proving very useful to me at the moment...

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


----------



## schielrn (Mar 4, 2014)

Jon von der Heyden said:


> 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 (Mar 4, 2014)

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 (Mar 4, 2014)

AngelJ said:


> 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 (Mar 4, 2014)

schielrn said:


> 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 (Mar 4, 2014)

AngelJ said:


> 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 (Mar 10, 2014)

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 (Mar 10, 2014)

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 (Mar 11, 2014)

xenou said:


> Setting my default fill color for "input cells"


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



xenou said:


> 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 (Mar 11, 2014)

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 (Mar 11, 2014)

xenou said:


> 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!


----------



## TinaP (Mar 11, 2014)

xenou said:


> 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



Nice! I learned my new thing for the day.  Now, if I learn something else today, it'll be a bonus day.


----------



## xenou (Mar 11, 2014)

> Nice! I learned my new thing for the day. Now, if I learn something else today, it'll be a bonus day.



In a pinch you can always pick up a new keyboard shortcut.    I really like _Control + Shift + Spacebar_ (select current region).


----------



## TinaP (Mar 11, 2014)

xenou said:


> In a pinch you can always pick up a new keyboard shortcut.    I really like _Control + Shift + Spacebar_ (select current region).


And now I've learned something else.  It's a great day!


----------

