Excel VBA Tips & Tricks

[adsense slot=”5402167102″ width=”468″]

Am consolidating here some commonly required Excel VBA snippets in an FAQ form.

How to find the last row in a Worksheet?

How to clear the clipboard after a copy/paste operation?

Why am I not able to copy a range?

If I get a Column number, how can I get the Column alphabet?

Is there something else you would like me to add here? Just leave a comment.

5 thoughts on “Excel VBA Tips & Tricks

  1. Need to add logic if ColumnNumber Mod 26 = 0:

    http://codesnip.net/excel-vba-tips-tricks-faq

    ‘First the function
    Function CChr(ColumnNumber As Integer) As String
    Dim FirstChar As Integer
    FirstChar = (ColumnNumber – ColumnNumber Mod 26) / 26
    If FirstChar > 0 Then
    ColumnNumber = ColumnNumber Mod 26

    >>>> If ColumnNumber = 0 then ColumnNumber = 26

    CChr = Chr(64 + FirstChar)
    End If
    CChr = CChr & Chr(64 + ColumnNumber)
    End Function

  2. Seems to me this is not slower, jet shorter:

    Function cchr(i As Integer) As String
    cchr = Range(Cells(1, i), Cells(1, i)).Address
    cchr = Mid(cchr, 2, Len(cchr) – 3)
    End Function

    But I wouldn’t be surprised if there’s an even better way.

  3. The CChr function should pass in byval, so that the value is not changed. Also, the logic for the modulo function needs some tweaks. This code also spits out an error value if the range is invalid. Try:

    Function CChr(ByVal ColumnNumber As Integer) As String Use Byval so that the calling value is not changed!

    Dim FirstChar As Double

    If (ColumnNumber 0) And (ColumnNumber = 1 Then
    ColumnNumber = ColumnNumber Mod 26
    If ColumnNumber = 0 Then ColumnNumber = 26
    CChr = Chr(64 + Int(FirstChar))
    End If
    CChr = CChr Chr(64 + ColumnNumber)
    Else
    CChr = InvalidColNum
    End If
    End Function

  4. Previous post did not take some lines?
    The CChr function should pass in byval, so that the value is not changed. Also, the logic for the modulo function needs some tweaks. This code also spits out an error value if the range is invalid. Try:

    Function CChr(ByVal ColumnNumber As Integer) As String ‘Use Byval so that the calling value is not changed!

    Dim FirstChar As Double

    If (ColumnNumber > 0) And (ColumnNumber = 1 Then
    ColumnNumber = ColumnNumber Mod 26
    If ColumnNumber = 0 Then ColumnNumber = 26
    CChr = Chr(64 + Int(FirstChar))
    End If
    CChr = CChr & Chr(64 + ColumnNumber)
    Else
    CChr = “InvalidColNum”
    End If
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *