Home » Blogs

Vishesh's blog

Vishesh's picture

Advanced Filter

Submitted by Vishesh on 9 January, 2010 - 21:49

With advanced filter in Excel using menu while trying to find out Unique, the limitation is that the destination range should be on the same sheet as the source. However, using VBA there is no such limitation. Using VBA the source and destination ranges in Advanced filter need not be on the same sheet.

»
Vishesh's picture

Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel

Submitted by Vishesh on 9 January, 2010 - 18:04
In VBA, to Copy Values from range A1:A5 to B1 you will generally write 
Sheet1.Range("A1:A5").Copy
 
Sheet1.Range("B1").PasteSpecial xlPasteValues
Another method: write the following function in the code window 
Sub CopyValues(rngSource As Range, rngTarget As Range)
 
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
 
End Sub
and call the above function using the following line of code wherever you need to paste values 
Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
The so
»

Syndicate content