Most frequently used piece of VBA

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:

Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Deleting blank rows

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
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:

Code:
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
 
Last edited:
You can use something like this instead:
Code:
    Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Denis
 
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.
 
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
 
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
 
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:

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
 
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
Code:
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
 
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:
Code:
v=selection.formula
3. Hit ENTER
4. Select the range where you want to paste
5. In the immediate window:
Code:
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...
 

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top