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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Deleting blank rows

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

RobMatthews

Board Regular
Joined
Nov 16, 2008
Messages
81
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:

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You can use something like this instead:
Code:
    Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Denis
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,048
Office Version
2010
Platform
Windows
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
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
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,218
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top