Most frequently used piece of VBA

[Lewiy]

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]

Deleting blank rows

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

 

[RobMatthews]

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]

You can use something like this instead:

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

Denis

 

[Jon von der Heyden]

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]

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]

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]

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]

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]

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