Most frequently used piece of VBA
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Most frequently used piece of VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,282
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Most frequently used piece of VBA

    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
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most frequently used piece of VBA

    Deleting blank rows

    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular RobMatthews's Avatar
    Join Date
    Nov 2008
    Location
    Brisbane, AU
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most frequently used piece of VBA

    Quote Originally Posted by Jonmo1 View Post
    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 by RobMatthews; Feb 10th, 2014 at 04:36 PM.

  4. #4
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most frequently used piece of VBA

    You can use something like this instead:
    Code:
        Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  5. #5
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,679
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Most frequently used piece of VBA

    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.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  6. #6
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most frequently used piece of VBA

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,069
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Most frequently used piece of VBA

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Most frequently used piece of VBA

    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
    Richard Schollar

    Using xl2013

  9. #9
    Board Regular ZAX's Avatar
    Join Date
    Jul 2012
    Location
    Range("A1")
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most frequently used piece of VBA

    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
    - -={Laziness is the mother of invention!}=- -
    For some fun, read the jokes in the following thread: "Funny jokes folks"
    Or you can check my "Shapes and faces" thread.
    My Email is "Toughkid999@hotmail.com"

  10. #10
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,679
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Most frequently used piece of VBA

    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...
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •